www.webdeveloper.com
Results 1 to 4 of 4

Thread: Can't get my JOIN syntax right, any help please.

  1. #1
    Join Date
    Oct 2007
    Posts
    126

    Can't get my JOIN syntax right, any help please.

    Hi I can't fathom this at all. I need to write a SELECT query and I need the correct join to get results from two tables here they are:

    Table: comments
    PHP Code:
    -----------------------------------------
    |  
    id  |  name content       approved |
    -----------------------------------------
    1    Mr A   Some txt    1          |
    2    Mr B   Some text   1          |
    3    Mr C   Text        0          |
    4    Mr D   more txt    0          |
    5    Mr E   lots o txt  1          |
    ------------------------------------------ 

    Table: comments_meta
    PHP Code:
    -----------------------------------------
    |  
    id |  com_id       meta_key    meta      |
    -----------------------------------------
    1   1             title       My title  |
    2   1             Status      6         |
    3   2             title       A title   |
    4   2             Status      6         |
    5   3             title       The title |
    6   3             Status      5         |
    7   4             title       Title     |
    8   4             Status      5         |
    9   5             title       Titles    |
    10  5             Status      5         |
    ------------------------------------------ 
    The tables can be joined by comments.id = comments_meta.com_id. So for each person in the comments table there are two rows in the comments_meta table.

    The only results I want to pull are name and content from comments as long as Approved = 1 joined with meta(title) from comments_meta as long as meta(status) = 6

    Therefore the return should be
    Mr A, Some txt, My title
    Mr B, Some text, A title

    any other results for Mr C through to Mr E are not required. Although Mr E has approval of 1, his meta(status) is only 5 and not 6 therefore he is not required.

    I hope it makes sense and unfortunately I can't change the table structure to make it simpler. Anyone got any clues on what the SQL SELECT statement should be? I've tried using LEFT RIGHT INNER OUTER and OVER THE HILL AND ROUND THE BACK joins and nothing!

    Thanks for looking.

    Chris
    Last edited by cannon303; 04-25-2011 at 08:55 AM.

  2. #2
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    SELECT cm.*, c.*
    FROM comments c LEFT OUTER JOIN comments_meta cm ON cm.com_id = c.id
    WHERE c.approved = 1 AND cm.meta_key = 'STATUS' AND cm.meta = 6

    Untested. LMK if it works.

  3. #3
    Join Date
    Oct 2007
    Posts
    126
    Hey thanks very much for trying. Unfortunately it didn't work. I think I need to be SELF JOINING the two rows from comments_meta and then joining to comments. hmmm the quest continues...

  4. #4
    Join Date
    Oct 2007
    Posts
    126
    Hi still stuck with this. I managed to get this to work as long as there is only one result that is true. So basically change Mr B's approved to 0 and therefore leaving the only result to pull from the database being:

    Mr A, Some txt, My title

    then this SELECT will work:
    PHP Code:
    SELECT c1.name AS autc1.id AS cidcm1.meta AS tit
                      FROM comments c1
    comments_meta cm1 WHERE c1.approved AND c1.id 
                (
    SELECT cm1.com_id AS cid FROM comments_meta cm1
                                    INNER JOIN comments_meta cm2 ON cm1
    .com_id cm2.com_id
                                    WHERE cm2
    .meta=AND cm1.meta_key ='title')
    AND 
    cm1.com_id c1.id AND cm1.meta_key 'title' 
    That works fine but as soon as there are two sets of results that equate to being true no results come back.
    Last edited by cannon303; 04-25-2011 at 06:56 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles