SELECT from multiple tables

[pengi]

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):

 

table1:

 

pid      desc

================

1001     Apple

1002     Orange

1003     Papaya

 

table2:

 

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!

 

--

Pengi.

 

[jazzman1]

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.

Edited October 17, 2013 by jazzman1

[pengi]

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

 

Apple

Apple

Orange

Papaya

 

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

 

--

Pengi.

[DavidAM]

SELECT `desc`, COUNT(*)
FROM table1 JOIN table2 ON table1.pid = table2.pid
WHERE table2.cat IN (101, 102)
GROUP BY `desc`
HAVING COUNT(*) = 2

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.