SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help With A query/join in MySql

    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.

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT
    	p.phone_id_num,
    	p.phone_model,
    	p.phone_name
    FROM 
    	phone_data p 
     
    INNER JOIN 
    	phone_categories c 
    ON 
    	c.phone_id = p.phone_id_num 
     
    WHERE 
    	c.cat_id IN(3, 29)

  3. #3
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Furicane,
    I added the group & Count lines, and it worked like a charm. Now I may be able to sleep tonight.......


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •