Jump to content

Archived

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

pengi

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

 

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.

 

Share this post


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.

Share this post


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

 

Apple

Apple

Orange

Papaya

 

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

 

--

Pengi.

Share this post


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`
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.

Share this post


Link to post
Share on other sites

×

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.