I Have an SQL problem. I have two tables of data in a database about cell phones. The first table lists a phones unique ID number, with the categories that the phone is listed in (phones usually listed in more than 1 category)
The second table contains the data on the phones, with the phones unique id number in the first column.
phone_categories
|phone_id | cat_id|
| 1 | 29 |
| 1 | 5 |
| 1 | 3 |
| 2 | 3 |
| 2 | 1 |
| 3 | 1 |
| 4 | 3 |
| 4 | 29 |
phone_data
|phone_id_num | phone_model | phone_name |
| 1 | Motorola | Milestone |
| 2 | Apple | iPhone |
| 3 | Samsung | Galaxy |
| 4 | HTC | Hero |
What I need is to be able to select such that I can get the data on all phones that are listed in both categories 3, and 29. The query below does a good job of filtering the data.
SELECT a.phone_id
FROM phone_categories a
WHERE a.cat_id
IN (
‘3’, ‘29’
)
GROUP BY a.phone_id
HAVING COUNT( a.phone_id ) =2
LIMIT 0 , 30
Gives the following results
phone_id |
---|
1 |
4 |
The filter part works fine. It returns only the id numbers of the phones that are in both category 3 and 39.
My problem…I need to join the 2 tables so that I can list out the selected data including the phone_data table. I am stumped. I have tried every join that I know, and I can’t get it to work. I am thinking that I am missing something obvious.
If you can set up this join, or If you know of another way to do filter the data please let me know.