Select from two tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select from two tables

Author

Topic

Dani_87
Starting Member

14 Posts

Posted - 2012-05-04 : 01:13:22

I want ONLY the IDs who have on status D and P in year *2010/2011*
To get status D ID the status has to be *D* and transfer_date as *NULL* (in year *2010/2011*) (FROM TABLE_ED)
To get IDs who have status as P is just all IDs from TABLE_p (in year *2010/2011*)
ID can have status as *E* but ONLY *prior to 2010*.
(NOTHING IN 2012)

Any help greatly appreciated


TABLE_ED

ID	YEAR	TERM	STAT	TRANSFER_DATE
1	2011	1101	E	13-Feb-10
1	2010	1002	D	NULL
1	2008	802	E	13-Mar-08
2	2012	1202	E	13/03/2012
2	2011	1103	D	NULL
3	2008	802	E	13/03/2012
3	2010	1004	E	13/04/2010
4	2010	1005	D	13/05/2010
4	2011	1006	D	NULL

TABLE P


ID	YEAR	TERM
5	2010	1006
7	2012	1209

WANT:
ID 1 no because it has status *E* in *2011*
ID 2 no has *2012*
ID 3 no has *E* in *2010*
ID 4 YES has status *D* and transfer date *NULL*
ID 5 and YES because every ID in TABLE_P has status *P*
ID 7 NO has 2012

ID
4
5
7

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-05-04 : 07:50:19

Either of these 2 queries might solve your problem:


select a.id
  from table_ed as a
 where a.stat='D'
   and a.year in (2010,2011)
   and a.transfer_date is null
   and not exists (select b.id
                     from table_ed as b
                    where b.stat<>'D'
                  )
union all
select a.id
  from table_p as a
 where a.year in (2010,2011)
   and not exists (select b.id
                     from table_p as b
                    where b.year not in (2010,2011)
                  )


select id
  from table_ed
 group by id
 having sum(case when stat='D' and year in (2010,2011) and transfer_date is null then 0 else 1 end)=0
union all
select id
  from table_p
 group by id
 having sum(case when year in (2010,2011) then 0 else 1 end)=0

These queries were not tested, so there might by type/syntax errors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources