Sub-queries, joins in one query

Question

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 1

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.