CODEWITHSUNDEEP
phpmysql
Matt Cole
Matt Cole

Reputation: 17

adding table to an existing query

I have a table simsu_subs that has fields user_id and expiry date. That same field name, user_id is used in another table, mt_links.

I have this query working perfectly:

SELECT link_published,
    CASE link_published WHEN 1 THEN 'Published' ELSE 'No' END AS 'Published/no',
    u.firstname,
    u.lastname,
    accreditations,
    title_descriptor,
    phone_number,
    display_email,
    u.website,
    avatar,
    logo,
    disclaimer,
    signature_ie_john_doe,
    MAX(IF(lv.cf_id =24, lv.`value`, '')) AS `UploadGraphic`,
    MAX(IF(lv.cf_id =36, lv.`value`, '')) AS `Color`,
    MAX(IF(lv.cf_id =37, lv.`value`, '')) AS `BodyCopy`,
    MAX(IF(lv.cf_id =39, lv.`value`, '')) AS `Salutation`,
    l.link_name,
    MAX(IF(lv.cf_id =38, lv.`value`, '')) AS `Magazine`,
    address,
    MAX(IF(lv.cf_id =35, lv.`value`, '')) AS `Address 2`,
    city,
    state,
    postcode FROM cwpc_jsn_users u 
    INNER JOIN cwpc_mt_links l ON u.`id` = l.`user_id`
    INNER JOIN cwpc_mt_cfvalues lv ON l.link_id = lv.link_id
GROUP BY u.firstname, l.link_name

This is the query I want to add:

SELECT b.expiry_date
FROM cwpc_simsu_subs b
JOIN cwpc_mt_links c
ON b.user_id = c.user_id

I would like to add expiry_date to this query, joined by user_id. I've tried to alias the field and inner join in more ways than I care to count. I just can't get it to work. I'm thinking the line

INNER JOIN cwpc_mt_links l ON u.id = l.user_id

has something to do with it? Not completely sure though.

Can someone point me in the right direction?

Thx

Upvotes: 1

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

The alias l is already being used, so that might be your problem. I would expect your FROM clause to look like:

FROM cwpc_jsn_users u 
     INNER JOIN cwpc_mt_links l ON u.`id` = l.`user_id`
     INNER JOIN cwpc_mt_cfvalues lv ON l.link_id = lv.link_id 
     INNER JOIN cwpc_simsu_subs b ON b.user_id = u.id

Upvotes: 1

Related Questions