Jump to content


Photo

SELECT from multiple tables

mysql select

  • Please log in to reply
3 replies to this topic

#1 pengi

pengi

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 17 October 2013 - 05:35 AM

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.

 



#2 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,660 posts
  • LocationMississauga, Canada

Posted 17 October 2013 - 08:30 AM

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 by jazzman1, 17 October 2013 - 08:32 AM.


#3 pengi

pengi

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 20 October 2013 - 06:01 AM

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.



#4 DavidAM

DavidAM

    Advanced Member

  • Gurus
  • 1,974 posts
  • LocationSpring, TX USA

Posted 20 October 2013 - 04:43 PM

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.
-- I haven't lost my mind, it's backed up on tape ... somewhere!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com