Help with Subquery

[Smilie]

SQL Subqueries

Can anyone tell me what is wrong with this complex query? It works fine without the highlighted subquery. The subquery works fine on its own. But they don't work together.

I think that I might not be able to call multiple results from a subquery but I'm not sure about that. Does any one know???

Any help will be much appreciated!
Thanks!
select k.site_id, s.name, t.date_opened,

(select concat(c.name_first,' ', c.name_last) as User from sitepack_approval sa
left join sitepack_approval_users spa on sa.approval_id=spa.approval_id
left join contacts c on spa.user_id=c.contact_id where sa.site_id=k.site_id and spa.timestamp is null
order by spa.level limit 1) as pending_approver,

(select sat.name from sitepack_approval sa left join sitepack_approval_types sat
on sa.approval_type_id=sat.approval_type_id where sa.site_id=k.site_id order by sa.approval_id desc limit 1)
as sitepack_type,

(select t.site_id, D.name AS Division, R.name AS Region, M.name AS Market,
concat(C1.name_first, " ",C1.name_last) AS RD,
concat(C2.name_first," ",C2.name_last) AS DD
FROM stores as t LEFT JOIN regions as M ON t.region_id = M.region_id
LEFT JOIN regions AS R ON M.parent_region_id = R.region_id
LEFT JOIN regions AS D ON R.parent_region_id = D.region_id
LEFT JOIN contacts AS C1 ON R.contact_id = C1.contact_id
LEFT JOIN contacts AS C2 ON D.contact_id = C2.contact_id) as site, Divsn, Reg, Mkt, RDs, DDs,

concat(cd.name_first,' ',cd.name_last) as 'DRE'

FROM sitepacks k LEFT JOIN sites s on k.site_id=s.site_id
LEFT JOIN stores t on k.site_id=t.site_id
LEFT JOIN contacts cd on k.dre_contact_id=cd.contact_id
WHERE status_id=(select status_id from sitepack_status where description='Approving' and sitepack_type <> 'Renewal')
and t.status_code <> 'DEAD'
and t.date_opened > now();

 

[Smilie]

Problem Solved

I figured it out. I was right. It seems that I can't do a subquery with multiple fields. I had to break that query apart and it seems to work now. Here is the solution:

select k.site_id, s.name, st.date_opened,

(select concat(c.name_first,' ', c.name_last) as User from sitepack_approval sa
left join sitepack_approval_users spa on sa.approval_id=spa.approval_id
left join contacts c on spa.user_id=c.contact_id where sa.site_id=k.site_id and spa.timestamp is null
order by spa.level limit 1) as pending_approver,

(select sat.name from sitepack_approval sa left join sitepack_approval_types sat
on sa.approval_type_id=sat.approval_type_id where sa.site_id=k.site_id order by sa.approval_id desc limit 1)
as sitepack_type,

(SELECT D.name FROM stores t LEFT JOIN regions as M ON t.region_id = M.region_id
LEFT JOIN regions AS R ON M.parent_region_id = R.region_id
LEFT JOIN regions AS D ON R.parent_region_id = D.region_id where t.site_id=k.site_id) as Division,

(SELECT R.name
FROM stores t LEFT JOIN regions as M ON t.region_id = M.region_id
LEFT JOIN regions AS R ON M.parent_region_id = R.region_id where t.site_id=k.site_id) as Region,

(SELECT M.name
FROM stores t LEFT JOIN regions as M ON t.region_id = M.region_id where t.site_id=k.site_id) as Market,

(select concat(C1.name_first, " ",C1.name_last)
FROM stores as t LEFT JOIN regions as M ON t.region_id = M.region_id
LEFT JOIN regions AS R ON M.parent_region_id = R.region_id
LEFT JOIN regions AS D ON R.parent_region_id = D.region_id
LEFT JOIN contacts AS C1 ON R.contact_id = C1.contact_id where t.site_id=k.site_id) as RD,

(select concat(C2.name_first," ",C2.name_last)
FROM stores as t LEFT JOIN regions as M ON t.region_id = M.region_id
LEFT JOIN regions AS R ON M.parent_region_id = R.region_id
LEFT JOIN regions AS D ON R.parent_region_id = D.region_id
LEFT JOIN contacts AS C1 ON R.contact_id = C1.contact_id
LEFT JOIN contacts AS C2 ON D.contact_id = C2.contact_id where t.site_id=k.site_id) as DD,

concat(cd.name_first,' ',cd.name_last) as 'DRE'

FROM sitepacks k
LEFT JOIN stores st on k.site_id=st.site_id
LEFT JOIN sites s on k.site_id=s.site_id
LEFT JOIN contacts cd on k.dre_contact_id=cd.contact_id
WHERE status_id=(select status_id from sitepack_status where description='Approving' and sitepack_type <> 'Renewal')
and st.status_code <> 'DEAD'
and st.date_opened > now();