selecting data from more than two tables

[washaw]

Hello

I have four tables

create table tbl1 (id int,  value varchar(100))

insert into tbl1
select 1, 'aa'
union all
select 2, 'bb'
union all select 3, 'cc'

create table tbl2 (id int,  id2 int, value varchar(100))

insert into tbl2
select 1, 1, 'dd'
union all
select 2, 1, 'ee'
union all select 3, 2, 'ff'

create table tbl3 (id int,  id2 int, value varchar(100))

insert into tbl3
select 1, 3, 'gg'
union all
select 2, 3, 'hh'

create table tbl4 (id int,  id2 int, type varchar(100))

insert into tbl4
select 1, 1, 'eef'
union all
select 2, 3, 'eec'

tbl2 and tbl3 are children of tbl1 and tbl4 is a child of tbl3

I want to get records from tbl1 where the record should exist either in tbl2 or tbl3 or both but if it exists in tbl3 its type should be of 'eec' from tbl4

Thanks

 

[r937]

select tbl1.id
     , tbl1.value
  from tbl1
inner
  join (
       select id2
         from tbl2
       union 
       select id2
         from tbl3
       inner
         join tbl4 
           on tbl4.id2 = tbl3.id
          and tbl4.type = 'eec' 
       ) as foo
    on foo.id2 = tbl1.id

r937.com | rudy.ca

 

[kaht]

Does tbl4 even need to exist?

select t1.id,
   t1.value,
   t2.id2,
   t2.value,
   t3.id2,
   t3.value,
   case when (t3.id is null) then 'eef' else 'eec' end
from tbl1 t1
   left join tbl2 t2
   on t2.id = t1.id
   left join tbl3 t3
   on t3.id = t1.id

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]

 

[Bubbles2610]

SELECT *
FROM tbl1
WHERE EXISTS (SELECT 1
FROM tbl2
WHERE tbl2.id2 = tbl1.id)
OR EXISTS (SELECT 1
FROM tbl3
WHERE tbl3.id2 = tbl1.id
AND EXISTS (SELECT 1
FROM tbl4
WHERE tbl4.id2 = tbl3.id
AND tbl4.type = 'eec'))

 

[washaw]

Thank you all,

Here is my start

select tbl1.*
from tbl1 left join tbl2
on tbl1.id = tbl2.id2
left join tbl3
on tbl1.id = tbl3.id2
where coalsce(tbl2.id, tbl3.id) is not null

How do I include tbl4 in my code so that only those records from tbl3 of type 'eec' should be selected

Thanks,

 

[r937]

warshaw, did you even try my solution, or are you ignoring it on purpose?

r937.com | rudy.ca