Multi-Table Query Returning Duplicate Results

[nodirtyrockstar]

Can anyone help me understand why the following query:

 

SELECT products.id, merch.id FROM products, merch;

 

Would return the results from the product table two times, and the results from the merch table three times?

 

Right now there is just dummy content in my tables (two products in one, and three in the other), and there is no duplication. Let me know if you need more information. I am sure (hoping) it is something simple that I am doing wrong.

[DavidAM]

Without a JOIN, that query will return a Cartesian product. Every row in "products" will be matched with every row in "merch". You need to specify how the tables are related:

 

SELECT products.id, merch.id
FROM products JOIN merch ON products.preferred_merchant_id = merch.id

 

Of course the columns I used in the above query are just examples, the actual JOIN condition would depend on the structure of your tables.

[nodirtyrockstar]

Merch contains one type of product, and products contains another type of product. (I need to rename it, I know...)

 

CREATE TABLE `products` (\
`img` varchar(20) NOT NULL default '../img/albumArt2.gif',
`id` varchar(20) NOT NULL, 
`artist` varchar(30) NOT NULL,
`title` varchar(30) NOT NULL,
`label` varchar(30) NOT NULL,
`year` year(4) NOT NULL,
`price` decimal(11,2) unsigned NOT NULL,
`qty` int(11) unsigned NOT NULL,
`desc` varchar(255) NOT NULL,
`agedOff` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `merch` (
`img` varchar(20) NOT NULL default '../img/albumArt2.gif',
`id` varchar(20) NOT NULL,
`title` varchar(30) NOT NULL,
`size` varchar(3) NOT NULL,
`color` varchar(10) NOT NULL,
`sex` varchar(6) NOT NULL default 'Unisex',
`price` decimal(11,2) unsigned NOT NULL,
`qty` int(11) unsigned NOT NULL,
`desc` varchar(255) NOT NULL,
`agedOff` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1

What I would like to do is just get a list of all the product IDs in both tables.

Edited November 16, 2012 by nodirtyrockstar

[Barand]

You need a UNION. The layouts are different so you will need to specify which ones you need as the corresponding columns in each part of the union need to be of the same type

 

eg

 

SELECT price, qty, description, artist FROM products
UNION
SELECT price, qty, description, NULL FROM merch

 

merch table does not have artist so NULL substituted in this example so the two parts are the same

[nodirtyrockstar]

What if all I want is a list of all of the IDs?

 

SELECT `id` FROM products
UNION
SELECT `id` FROM merch;

 

??

[nodirtyrockstar]

Yes! Tried it and it worked. Thanks for the tip.

[Barand]

If there is a possibility of identical rows(same ids) from both selects, UNION will only show one of them unless you use UNION ALL. If you are just doing ids I'd do it this way so you know which table they belong to

SELECT id, 'P' as tablename FROM products
UNION
SELECT id, 'M' as tablename FROM merch
ORDER BY id