SQL INTERVIEW QUESTION | Join 3 Tables & Filter Records in 2 Tables Only | SQL

[@edgydodgy3417]

Awesome! Content It requires a lot of hardwork for producing the content. Thankyou so much

[@datasculptor2895]

Thank you. Please like share and subscribe

[@UnrealAdi]

Hello bud,
are we under the strict instructions to only 'JOIN' the tables or would it be ok to use 'Union'?
Also, may I know where was this interview question asked?
Thanks for the continuous content uploads, it helps a ton of folks!

[@datasculptor2895]

I do not remember where this was asked, but the question was to achieve results using joins

[@shyamshivakumar7807]

Is the below approach also fine?
with cte as (
select * from TableA
union all
select * from TableB
union all
select * from TableC
)
select ID,ValueA,count(*) as cnt
from cte
group by ID,ValueA
having cnt=2
The only thing here is column name is taken as ValueA as in the UnionAlll the first table is TableA

[@datasculptor2895]

It will work if there are no duplicates in tableA

[@anirbanbiswas7624]

simple approach-----
with cte as (select a.id as id,a.valuea as vals from tablea2 as a join tableb as b
on a.id=b.id and a.valuea=b.valueb),
cte2 as(select b.id as id,b.valueb as vals from tableb as b join tablec as c
on b.id=c.id and b.valueb=c.valuec)
select c1.* from cte as c1 left join cte2 as c2 on c1.id=c2.id

[@datasculptor2895]

Will this work if there is a common entry in all the 3 tables?

[@bollyhome6650]

Cant we do this by two inner join's it will give common between all three tables

[@datasculptor2895]

No. Output should be present only in 2 tables

[@harshbhoyar7176]

is this correct ?
with cte as (
SELECT a.id , a.ValueA FROM TABLEA A JOIN TableB B ON A.ID=B.ID AND A.VALUEA=B.VALUEB
union
select b.ID , b.ValueB from tableb b join tablec c on b.id=c.ID and b.ValueB=c.ValueC
union
select a.ID, a.ValueA from TableA a join TableC c on a.ID=c.id and a.ValueA=c.ValueC)

select * From cte;

[@datasculptor2895]

Unfortunately this will not work. Row common in all the 3 tables will also appear in this

[@harshbhoyar7176]

@@datasculptor2895 thanks

[@devendrabarasker4987]

is this a right approach using join?
with cte as(
select a.ID as id1,a.ValueA,b.ID as id2,b.ValueB,c.ID as id3,c.ValueC,case when a.ID=b.ID and a.ValueA=b.ValueB then 0
when b.ID=c.ID and b.ValueB=c.ValueC then 1
when c.ID=a.ID and c.ValueC=a.ValueA then 2 end flag,
coalesce (case when a.ID=b.ID and b.ID=c.ID then 5 end,4) flag2
from TableA a FULL join TableB b
on a.ID=b.ID AND A.ValueA=B.ValueB full join TableC c
on b.ID=c.ID and b.ValueB=c.ValueC full join TableA aa
on c.ID=aa.ID and c.ValueC=aa.ValueA
)select id2,valueb from cte
where flag25 and flag in (0,1)

[@datasculptor2895]

Yes. Please subscribe to my channel

[@devendrabarasker4987]

@@datasculptor2895 already i subscribed it bro you are making excellent content 🙌🙌

[@datasculptor2895]

@@devendrabarasker4987 Happy that you are liking my content. More interesting questions coming. Meanwhile you can let me know if you are looking out for any topics or questions.

[@devendrabarasker4987]

@@datasculptor2895 power bi advanced level if possible...

[@datasculptor2895]

@@devendrabarasker4987 Sure.. definitely.