Biswa Biswa - 3 years ago 195
SQL Question

Sub-queries, joins in one query

I have five tables. I need to get data from all of them. Table 'Tenancy_histories' contains the move_in_date, move_out_date, rent columns. 'Profiles' contains the first_name, last_name, email, profile_id etc. 'Referrals' contains the the referrer_bonus_amount and similar other data. Most importantly it contains the number of referrals made by a particular profile_id which is the number of occurence of that profile_id in the 'referrer_id(same as profile id)' column. 'Employment_details' contains the latest employer, occupationalcategory

I need to Write a query to display profile id, full name, phone, email id, city , house id, move_in_date ,move_out date, rent, total number of referrals made, latest employer and the occupationalcategory of all the tenants living in some particular city in the time period of jan 2015 to jan2016 sorted by their rent in descending order
Tried something like this:

select pr.first_name+' '+pr.last_name as full_name,
pr.email,
pr.phone,
pr.profile_id,
th.house_id,
th.move_in_date,
th.move_out_date,
th.rent,
ed.latest_employer,
ed.Occupational_category,
ref.cnt
from Profiles pr,
Tenancy_histories th,
Employment_details ed
INNER JOIN (select [referrer_id(same as profile id)],
count([referrer_id(same as profile id)]) as cnt
from Referrals
group by [referrer_id(same as profile id)]) as ref
on pr.profile_id = ref.[referrer_id(same as profile id)]
where pr.profile_id = th.profile_id
and th.profile_id = ed.profile_id
and pr.profile_id IN
(select profile_id
from Tenancy_histories
where move_in_date >= convert(date, 'Jan 2015')
and move_out_date <= convert(date, 'Jan 2016'))


Getting error:

The multi-part identifier "pr.profile_id" could not be bound. something is wrong in the inner join part . Maybe INNER JOIN is not the right way to retrieve the data

Answer Source

Is that what you want :

SELECT pr.first_name+' '+pr.last_name as full_name, 
       pr.email, 
       pr.phone, 
       pr.profile_id, 
       th.house_id, 
       th.move_in_date, 
       th.move_out_date, 
       th.rent, 
       ed.latest_employer, 
       ed.Occupational_category,  
       count(ref.profile_id) 
FROM Profiles pr
INNER JOIN Tenancy_histories th ON (pr.profile_id = th.profile_id AND move_in_date >= convert(date, 'Jan 2015') AND move_out_date <= convert(date, 'Jan 2016'))
INNER JOIN Employment_details ed ON (th.profile_id = ed.profile_id)
LEFT JOIN Referrals as ref ON (pr.profile_id = ref.profile_id)
GROUP BY pr.first_name+' '+pr.last_name, 
       pr.email, 
       pr.phone, 
       pr.profile_id, 
       th.house_id, 
       th.move_in_date, 
       th.move_out_date, 
       th.rent, 
       ed.latest_employer, 
       ed.Occupational_category

Note : You shouldn't use convert(date, 'Jan 2015') but something like convert(date,'20150101',112) instead because it can work on a server, and raise an error on another one... Search for "datetime implicit conversion" for more details about this.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download