0

I want to join a table to two tables and want result in below format,

ID    Name1    Name2     StatusOfName2Yearwise   Year

what I have tried is,

select t1.ID, t1.Name as 'Name1', t1a.Name as 'Name2', isnull(t2.Status,'N') as 'StatusOfName2Yearwise',Year
from
    table_1 t1, table_1 t1a
    LEFT JOIN table_3 t3 on t1a.id=t3.f_id
    LEFT JOIN table_2 t2  on t1a.ID=t2.id,
    table_4 t4 LEFT JOIN table_2 t2a on t4.Year=t2a.Fyr
where
     t1.ID=t3.f_id 
group by
    t1.ID, t1.Name, t1a.Name, t2.Status ,Year
order by
    t1.Name

This query is giving status of Name2 but it is NOT yearwise and I want it Yearwise. Please help if anybody knows..

Adding output data I got from query,

 ID           Name1        Name2        StatusOfName2Yearwise     Year
 22           George       Julie              C                   2015           
 22           George       Julie              C                   2016       
 22           George       Julie              C                   2017

expected result should be like below,

 ID           Name1         Name2        StatusOfName2Yearwise    Year
 22           George        Julie             N                   2015           
 22           George        Julie             N                   2016       
 22           George        Julie             C                   2017
Pawan Nogariya
  • 8,330
  • 12
  • 52
  • 105
Juliee
  • 49
  • 2
  • 8
  • Please add example data and expected output – juergen d Jun 11 '16 at 18:44
  • What do you mean "Yearwise"? Sample data and desired results would help. – Gordon Linoff Jun 11 '16 at 18:44
  • Please show the output you have(just an example, not all of it) and the output you are trying to achieve. It'll help clarify what you're trying to do – Dave Jun 11 '16 at 18:44
  • If you're going to give us example code, can you not rename everything. It makes it much more difficult to understand what you're trying to do. – Phill Jun 11 '16 at 18:48
  • These names give me a headache. – Dmitriy Khaykin Jun 12 '16 at 00:28
  • The only difference between expected output and what your are getting is that the `2015/2016` comes with the letter `C` instead of `N` .... without a screen shoot sample of the data from every table this is useless – Sufyan Jabr Jun 12 '16 at 05:38
  • 1
    Please provide the sample data from each table(1-4) Also, the questions coming to my mind are : 1.) From which table Year is coming. 2.) You are doing left join on table_3 and finally not selecting from table_3, make the join useless. 3.) table_4 and t2a also seems to be not doing anything useful – greperror Jun 12 '16 at 07:05
  • What database are you using? You added MySQL tag but have not removed sql-server tag. – Alex Jun 12 '16 at 07:40
  • Year is comming from table_4. and Joins are not useless because I want name which is not in table_3 so not selecting from it.. nd table_4 and t2a also required. But I am not getting proper data... if any persons status is C in any year it is giving as C for all years for that person not actual Status that is my problem.. – Juliee Jun 12 '16 at 07:57
  • But Year can also come from t2.Fyr? No need to join table_4 then. – LukStorms Jun 12 '16 at 08:08
  • see there is LEFT JOIN from table_4 that means i want all the years from table_4 that may or may not be present in t2a – Juliee Jun 12 '16 at 08:13
  • If you like, consider following this simple two-step course of action:1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Jun 12 '16 at 10:49

1 Answers1

0

Without knowing the data in the tables I tried to deduce what could be in it.

Based on that test data I constructed a SQL (Sql Server) that would give the expected results.

A full join on the table with the years makes sure that every year gets shown, even when there's no correspondent Fyr in table_2.

For the test the variable tables were given slightly more meaningfull names. 

DECLARE @table_1_user TABLE (id int, Name varchar(8));
DECLARE @table_2_userstatus TABLE (id int, Status char(1), Fyr char(4));
DECLARE @table_3_userrelation TABLE (id int, f_id int);
DECLARE @table_4_year TABLE (Year char(4));

insert into @table_1_user values 
(22,'George'),
(23,'Julie');
insert into @table_2_userstatus values 
(22,'C','2016'),
(22,'X','2017'),
(23,'N','2016'),
(23,'C','2017');
insert into @table_3_userrelation values (22,23);
insert into @table_4_year values ('2015'),('2016'),('2017');

select distinct
t1a.id as Id1,
t1b.id as Id2,
t1a.Name as Name1,
t1b.Name as Name2,
isnull(t2b.Status,'N') as StatusOfName2Yearwise,
q.Year
from (
     select * from @table_3_userrelation t3
     full outer join @table_4_year t4 on (1=1)
) q
left join @table_1_user t1a on (q.id = t1a.id)
left join @table_1_user t1b on (q.f_id = t1b.id)
left join @table_2_userstatus t2b on (q.f_id = t2b.id and q.Year = t2b.Fyr)
order by t1a.Name, t1b.Name, q.Year;

This will give the following results :

Id1     Id2     Name1       Name2       StatusOfName2Yearwise       Year
22      23      George      Julie       N                           2015
22      23      George      Julie       N                           2016
22      23      George      Julie       C                           2017
LukStorms
  • 28,916
  • 5
  • 31
  • 45