query help

[rjgapz]

$sql = ("
SELECT referral1.*, client.*, employee.*
FROM (
select r.referral_date,c.lastname,c.middlename,c.firstname,c.gender,r.presenting_problem,e.employee_nickname
from client c
inner join referral1 r
on c.referral_id = r.referral1_id
inner join assign_psychotherapist ap
on ap.a_referral_id = c.referral_id
inner join employee e
on ap.a_psychotherapist_id = e.empid
where referral_status ='Assigned' OR referral_status ='Accepted'
order by referral_date desc
) as table1
union all
SELECT referral1.*, client.*, volunteer.* 
FROM 
(
select r.referral_date,c.lastname,c.middlename,c.firstname,c.gender,r.presenting_problem,v.volunteer_nickname
from client c
inner join referral1 r
on c.referral_id = r.referral1_id
inner join assignvolunteer av
on av.Vreferralid = c.referral_id
inner join volunteer v
on av.Vvolunteerid = v.volid
where referral_status ='Assigned' OR referral_status ='Accepted'
order by referral_date desc
) as table2
 ");
$this->db->query($sql);
return $sql;

Error Number: 1051
Unknown table 'referral1'

[diafol]

You are selecting from the completed union, so there will be no referral1 table involved.

referral_date
lastname
middlename
firstname
gender
presenting_problem
employee_nickname

The above will be the column names from which you can select. But I'm not sure why you're selecting AGAIN. There is no need to have a containing SELECT clause as far as I can see.

I haven't checked the syntax carefully, but I suspect, you just need this:

(SELECT r.referral_date,c.lastname,c.middlename,c.firstname,c.gender,r.presenting_problem,e.employee_nickname AS nickname
FROM CLIENT c
INNER JOIN referral1 r
ON c.referral_id = r.referral1_id
INNER JOIN assign_psychotherapist ap
ON ap.a_referral_id = c.referral_id
INNER JOIN employee e
ON ap.a_psychotherapist_id = e.empid
WHERE referral_status ='Assigned' OR referral_status ='Accepted'
ORDER BY referral_date DESC
)
UNION ALL
(SELECT r.referral_date,c.lastname,c.middlename,c.firstname,c.gender,r.presenting_problem,v.volunteer_nickname AS nickname
FROM CLIENT c
INNER JOIN referral1 r
ON c.referral_id = r.referral1_id
INNER JOIN assignvolunteer av
ON av.Vreferralid = c.referral_id
INNER JOIN volunteer v
ON av.Vvolunteerid = v.volid
WHERE referral_status ='Assigned' OR referral_status ='Accepted'
ORDER BY referral_date DESC
)

Note the alias for nicknames

[rjgapz]

thank you for the clarification sir