Jump to content

SELECT from multiple tables


Recommended Posts

Hello all,


I have created a database with 2 tables table1 and table2 like this (except desc, all columns are integer type and the leftmost column is always the primary key):




pid      desc


1001     Apple

1002     Orange

1003     Papaya




key      pid     cat      


10001    1001    101

10002    1001    102

10003    1002    101

10004    1003    102


In other words, item 1001 (Apple) belongs to categories 101 and 102, while item 1002 (Orange) belongs to cat 101 only, and item 1003 (Papaya) belongs to cat 102 only.


Now I would like to make a SELECT statement that returns the name of the item(s) that belong to both categories 101 and 102 (in the above case, Apple). Any simple way (using as few statements as possible) to do this? Thanks!





Link to post
Share on other sites

The easiest way is to join these two tables and to use IN sql operator: 


SELECT `t1`.`desc` FROM table1 t1

INNER JOIN table2 t2 ON (t1.pid = t2.pid)

WHERE t2.cat IN (101,102)


Desc is a reserved word in mysql! You should avoid using reserved words as names of table columns.

Link to post
Share on other sites

Hi jazzman1,


Thank you for your reply and reminder (in fact, both desc and key should be avoided). However, I tried the above query and the result is







Seems to me that now when the item belongs to N categories, the names are printed N times.




Link to post
Share on other sites

SELECT `desc`, COUNT(*)
FROM table1 JOIN table2 ON table1.pid = table2.pid
WHERE table2.cat IN (101, 102)
GROUP BY `desc`
Anything that belongs to either 101 or 102 - but not both - will have a COUNT of 1 and will be excluded by the HAVING phrase.


Note: this will return entries that belong to 2 or more categories as long as two of them are 101 and 102.

Link to post
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.