SQL query not leaving out duplicate data

HybridSquirrel · Feb 29, 2016

I am having problems figuring out this SQL query. It keeps printing duplicate data into the table, but I only want unique data. Basically, I have 4 tables, maintable, table1, table2, table3. Tables 1-3 have a foreign key linked to maintable.

I am getting data like this:

Code:

Table1                  Table2                       Table3
Something here        table 2 stuff                table 3 stuff
Different               table 2 different stuff       Ditto
Different still         Same as above                  Ditto

But I want it to be displayed like this:

Code:

Table1                         Table2                                Table3
Something here           table 2 stuff                             table 3 stuff
Different                 table 2 different stuff        
still

Query:

Code:

SELECT DISTINCT o.name, d.name, a.name
  FROM maintable AS f 
  LEFT JOIN table1 AS o ON f.tableid=o.tab_id
  LEFT JOIN table2 AS d ON f.tableid=d.tab_id
  LEFT JOIN table3 AS a ON f.tableid=a.tab_id
  WHERE f.name = "Something"

I've tried various different types of joins. I even tried a subquery for the where clause. Can't seem to figure it out.

nickbits · Feb 29, 2016

I suspect something is screwy with your data. I thought it looked fine so I recreated your tables and entered some data and it worked as you expected. Can you post the contents of the tables?

Ken g6 · Feb 29, 2016

I don't think this is something you can do with normal SQL. Those columns are going to contain values no matter how you join them.

Depending on your particular flavor of SQL, if you have conditional and LAG functions, you could do something like this:

Code:

SELECT DISTINCT IIF(o.name=LAG(o.name),"",o.name) AS Table1, IIF(d.name=LAG(d.name),"",d.name) AS Table2, IIF(a.name=LAG(a.name),"",a.name) AS Table3
  FROM maintable AS f 
  LEFT JOIN table1 AS o ON f.tableid=o.tab_id
  LEFT JOIN table2 AS d ON f.tableid=d.tab_id
  LEFT JOIN table3 AS a ON f.tableid=a.tab_id
  WHERE f.name = "Something"

I think that might work in recent versions of MS SQL Server. What are you using?

slugg · Feb 29, 2016

Can you please reword your examples to match your sample query? I'm having a hard time understanding what you're actually seeing and what you expect.

HybridSquirrel · Feb 29, 2016

nickbits said:
I suspect something is screwy with your data. I thought it looked fine so I recreated your tables and entered some data and it worked as you expected. Can you post the contents of the tables?

I'm thinking that too. All 3 table's i'm trying to pull from have the same structure (just different names). They all have an ID(unique), name, desc, type, and foreignkey(tab_id, linked to main table). Maybe the issue i'm having is too much data linked to the same foreign key? But that shouldn't matter because I can query it individually and get the output I want.

Table 1 has 3 entries linked to maintable
Table 2 has 2,
Table 3 has 1.

Of course, it will have more than that eventually but for this test I just put in a few entries each. I can query each table individually and get the desired output, but something happens when I combined them together, I get a weird output.

The problem i'm having is that table 2 and 3 will just print out a repeat of the last thing they returned in the table. There might be an issue with my PHP? but whenever I query it directly on MySQL I get the same result as on the webpage.

My PHP looks like this:

Code:

     <?php while($tablequery = mysqli_fetch_array($data_query)):; ?>
        <tr>
          <td><?php echo $tablequery [0]; ?></td>
          <td><?php echo $tablequery [1]; ?></td>
          <td><?php echo $tablequery [2]; ?></td>
          </tr>
       <?php endwhile; ?>

slugg said:
Can you please reword your examples to match your sample query? I'm having a hard time understanding what you're actually seeing and what you expect.

I don't know how much more I can clarify. I showed my query and my desired table output, and the actually output I'm getting from the table. Sorry, i'm not trying to be rude I just don't know how else to explain it.

sao123 · Feb 29, 2016

Are you sure your primary keys and constraints are defined correctly? Your first set of results appears to be invalid.

Here is my sample database with queries, done in SQL Server Standard 2014 x64 SP3.

Code:

Pasta Table

ID	Pasta
1	Spaghetti
2	Fettuccine
3	Manicotti

Code:

Sauce Table

ID	PastaID	Sauce
1	1	MeatSauce
2	2	Alfredo
3	3	Marinara
4	2	Alfredo
5	1	MeatSauce
6	3	Marinara

Code:

Dairy Table

ID	PastaID	Dairy
1	3	Ricotta
2	2	Parmesan
3	2	Parmesan
4	3	Ricotta

Code:

Meat Table

ID	PastaID	Meat
2	2	Chicken
3	2	Chicken
4	2	Chicken

Code:

SELECT DISTINCT p.Pasta, s.Sauce, d.Dairy, m.Meat
  FROM Pasta AS p
  LEFT JOIN Dairy AS d ON p.ID=d.PastaID
  LEFT JOIN Meat AS m ON p.ID=m.PastaID
  LEFT JOIN Sauce AS s ON p.ID=s.PastaID

Produces
PASTA		SAUCE	  	DAIRY		MEAT
Fettuccine	Alfredo	  	Parmesan	Chicken
Manicotti	Marinara	Ricotta		NULL
Spaghetti	MeatSauce	NULL		NULL

Code:

SELECT DISTINCT p.Pasta, s.Sauce, d.Dairy, m.Meat
  FROM Pasta AS p
  LEFT JOIN Dairy AS d ON p.ID=d.PastaID
  LEFT JOIN Meat AS m ON p.ID=m.PastaID
  LEFT JOIN Sauce AS s ON p.ID=s.PastaID
WHERE p.Pasta = 'Spaghetti'

Produces
PASTA		SAUCE		DAIRY		MEAT
Spaghetti	MeatSauce	NULL		NULL

Code:

SELECT DISTINCT p.Pasta, s.Sauce, d.Dairy, m.Meat
  FROM Pasta AS p
  LEFT JOIN Dairy AS d ON p.ID=d.PastaID
  LEFT JOIN Meat AS m ON p.ID=m.PastaID
  LEFT JOIN Sauce AS s ON p.ID=s.PastaID
WHERE p.Pasta = 'Alfredo'

Produces
PASTA		SAUCE		DAIRY		MEAT
Fettuccine	Alfredo		Parmesan	Chicken

Code:

SELECT DISTINCT p.Pasta, s.Sauce, d.Dairy, m.Meat
  FROM Pasta AS p
  LEFT JOIN Dairy AS d ON p.ID=d.PastaID
  LEFT JOIN Meat AS m ON p.ID=m.PastaID
  LEFT JOIN Sauce AS s ON p.ID=s.PastaID
WHERE p.Pasta = 'Manicotti'

Produces
PASTA		SAUCE		DAIRY		MEAT
Manicotti	Marinara	Ricotta		NULL

Ken g6 · Mar 1, 2016

Ooh! If you can use PHP you can just store the results from each previous row and not print repeated strings. Be sure to use order by or more strings will be printed than necessary.

HybridSquirrel · Mar 1, 2016

sao123 said:
Are you sure your primary keys and constraints are defined correctly? Your first set of results appears to be invalid.

Here is my sample database with queries, done in SQL Server Standard 2014 x64 SP3.

Yeah, I think they are? Table's were setup as:

Code:

CREATE TABLE main(
	tableid int(11) NOT NULL AUTO_INCREMENT,
	name varchar(255) NOT NULL,
	description text,
	PRIMARY KEY (idnumber)
	) ENGINE=InnoDB;


CREATE TABLE table1(
	tableID int(11) NOT NULL AUTO_INCREMENT,
	name varchar(255) NOT NULL,
	type varchar(255) NOT NULL,
	description text,
	tab_id int(11),
	PRIMARY KEY (tableID),
	FOREIGN KEY (tab_id) REFERENCES main(tableid)
	) ENGINE=InnoDB;

Table 2 and 3 are an exact copy of table 1, just different contents like yours. It should be working like yours, but it isn't =\. I'm not that deep into this project so i'll just drop and re-create the tables and try again.

I'll look into the PHP code you mentioned Ken, but i'd really like to figure out the query first. Thanks for the help.

SQL query not leaving out duplicate data

HybridSquirrel

Diamond Member

nickbits

Diamond Member

Ken g6

Programming Moderator, Elite Member

slugg

Diamond Member

HybridSquirrel

Diamond Member

sao123

Lifer

Ken g6

Programming Moderator, Elite Member

HybridSquirrel

Diamond Member

TRENDING THREADS