Matt Cole Matt Cole - 3 months ago 8
MySQL Question

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

Answer

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