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

[cannon303]

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

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

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

[WolfShade]

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.

[cannon303]

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

[cannon303]

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:

SELECT c1.name AS aut, c1.id AS cid, cm1.meta AS tit
                  FROM comments c1, comments_meta cm1 WHERE c1.approved = 1 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=6 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.