Error in SQL syntax LEFT JOIN

[acctman]

I'm trying to create a query to where the code below will work... I typed out the general idea of what I'm trying to do

 

SELECT 
    entry_id, author_id, title, status
FROM exp_channel_titles
WHERE title LIKE %Member Package% 
AND status = 'complete'

LEFT JOIN SELECT
    entry_id, 
    field_id_14, field_id_15, field_id_25, field_id_27, field_id_28, field_id_29, 
    field_id_30, field_id_31, field_id_32, field_id_33, field_id_34, field_id_35
FROM exp_channel_data
WHERE entry_id = exp_channel_titles.entry_id

LEFT JOIN SELECT   
    member_id, email
FROM exp_members
WHERE member_id = exp_channel_titles.author_id

 

 

I tried something like this but got an error.

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.field_id_14, d.field_id_15, d.field_id_25, d.field_id_27, d.field_id_28, d.fiel' at line 3

SELECT 
    t.entry_id, t.author_id, t.title, t.status
    d.field_id_14, d.field_id_15, d.field_id_25, d.field_id_27, d.field_id_28, d.field_id_29, 
    d.field_id_30, d.field_id_31, d.field_id_32, d.field_id_33, d.field_id_34, d.field_id_35,
    m.member_id, m.email
FROM exp_channel_titles t
LEFT JOIN exp_channel_data d on t.entry_id = d.entry_id
LEFT JOIN exp_members m on t.author_id = m.author_id
WHERE t.title LIKE %Member% 
  AND t.status = 'complete'

[Barand]

Subqueries need to be enclosed in parentheses and table subqueries, like yours, need an alias. eg

 

LEFT JOIN (SELECT etc ) as X

[acctman]

Subqueries need to be enclosed in parentheses and table subqueries, like yours, need an alias. eg

 

LEFT JOIN (SELECT etc ) as X

 

here's what I got so far.... my out put is only should entry_id, author_id, title and status. What I need to show in the results is all the fields_* and email

SELECT 
    ect.entry_id,
    ect.author_id,
    ect.title,
    ect.status
FROM exp_channel_titles as ect

LEFT JOIN
(SELECT
    entry_id, 
    field_id_14,
    field_id_15,
    field_id_25,
    field_id_27, 
    field_id_28, 
    field_id_29, 
    field_id_30,
    field_id_31,
    field_id_32,
    field_id_33,
    field_id_34,
    field_id_35
FROM exp_channel_data) as ecd 
ON ecd.entry_id = ect.entry_id

LEFT JOIN
(SELECT   
    member_id,
    email
FROM exp_members) as exm
ON exm.member_id = ect.author_id

WHERE ect.title LIKE '%Member%' 
AND ect.status = 'complete'

[Barand]

Add those columns to the initial list of selected columns.

 

Why are you using subqueries?

[acctman]

Add those columns to the initial list of selected columns.

 

Why are you using subqueries?

 

do add it like this exm.email, ecd.field_id_14, ecd.field_id_15 ... and is there a better way to do it other than subqueries?

[Barand]

SELECT
         ect.entry_id
    ,    ect.author_id
    ,    ect.title
    ,    ect.status
    ,    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
WHERE ect.title LIKE '%Member%'
  AND ect.status = 'complete'

[smoseley]

Doesn't seem like you need a left join on members given the relationship of the tables.  An inner join would be quicker if you always have a member for a title.

[acctman]

I almost have the data I want... this is a bit tricky. (pic at bottom)

 

The query below returns all records with a status of Member-id5 but I need to filter that look up. if I did a simple query on ect. (exp_channel_titles) for author_id 323 I would get 5 results. Of those 5 results one has a status of 'Member-id5', another one has a status of 'complete' and title of %Member%. That's the deciding factor. If that row exist with status = complete and title = %Member% then its okay to grab the entry_id of row status = Member-id5 to use for looking up the data in ecd (exp_channel_data) with  in it.

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
WHERE ect.status = 'Members-id5'

[fenway]

Sorry, I don't follow.