CODEWITHSUNDEEP
mysql
acctman
acctman

Reputation: 4349

Adding to a LEFT JOIN query

Hi I need help adding a new CLAUSE, this one is pretty advanced for me and I can't figure it out. //using the ect.author_id query the exp_channel_titles table author_id column and scan those rows for the final result. To be determined by IF ect.status = 'Members-id5' AND ect.status = 'complete'... then use the row with Members-id5 for ect.entry_id. My question is how would I do that? I marked where I assume the new CLAUSE or maybe even query would go, but I could be wrong.

My problem is there is up to 5 different ect.entry_id so i'm trying to isolate the one in the row with status = Members_id5 but only if status = complete exist as well. This seems like something I would have to do in PHP with array's any thoughts?

    SELECT
             ect.entry_id
        ,    ect.author_id
        ,    ect.title
        ,    ect.status
        ,    ecd.field_id_13
        ,    ecd.field_id_14
        ,    ecd.field_id_15
        ,    ecd.field_id_25
        ,    ecd.field_id_27
        ,    ecd.field_id_28
        ,    ecd.field_id_29
        ,    ecd.field_id_30
        ,    ecd.field_id_31
        ,    ecd.field_id_32
        ,    ecd.field_id_33
        ,    ecd.field_id_34
        ,    ecd.field_id_35
        ,    exm.email
    FROM exp_channel_titles as ect
    LEFT JOIN exp_channel_data as ecd
      ON ecd.entry_id = ect.entry_id
    LEFT JOIN exp_members as exm
      ON exm.member_id = ect.author_id

// new CLAUSE

    WHERE ect.title LIKE '%Member%'
    AND ect.status = 'Members-id5'

Upvotes: 1

Views: 79

Answers (2)

Dan J
Dan J

Reputation: 16708

If I understand your problem correctly, you want to only return exp_channel_titles records where status = "Members-id5" but for whose author_id there exists another record whose status is "Complete".

If I have that right, I believe you just need to join (INNER JOIN, not LEFT (outer) JOIN) on the table once more:

JOIN exp_channel_titles as ect_status ON ect.author_id = ect_status.author_id AND ect.status = 'complete'

That clause, added to your existing query, should return only exp_channel_titles records whose status is "Members-id5" but which have a matching record whose status is "complete".

Upvotes: 2

octern
octern

Reputation: 4868

Based on your comment, it sounds like you want to find individuals who have one record where ect.status = "Members-id5" and another where ect.status = "complete" . You then want the SELECT query to apply just to those individuals. You can do that using two subqueries within the WHERE clause. Something like this: 

WHERE ect.title LIKE '%Member%'
AND ect.author_id IN 
     (select author_id from exp_channel_titles where ect.status = "Members-id5")
AND ect.author_id IN
     (select author_id from exp_channel_titles where ect.status = "complete")

The first subquery generates a list of all author IDs that have at least one record with "Members-id5" in it, and the IN clause checks to see if the author ID currently under consideration is one of those. The second one does the same with a list of all author IDs that have a record with "complete". If both conditions are true, then that means that individual is one whose records you want to return.

I'm not familiar with your data structure so this may not be the exact code you need (in particular, ect.author_id may not actually be the field to compare on), but hopefully this gives you the idea.

Upvotes: 1

Related Questions