SQL Query Help - 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

SQL Query Help

Author

Topic

its_me
Starting Member

6 Posts

Posted - 2012-09-11 : 21:42:23

I am able to get the joins working when the data is extracted from view and other tables. I need to add to this query by checking if one of the field in another table holds a specific value or not. If yes, I want to return – YES, else NO

I am not able to figure out how I can get this data in the same query. I tried left join, that returns all the data and does not consider the where clause

select view.name, view.item from view
inner join tableA on view.Col1 = tableA.t1 and view.Col2 = tableA.t2
inner join tableB on view.Col1 = tableB.t5 and view.Col2 = tableB.t2
and view.item = ‘ABC’
union
select view.name, view.item from view
inner join tableA on view.Col1 = tableA.t1 and view.Col2 = tableA.t2
left join problemTable on view.Col2 = problemTable.Col2???
where view.item = ‘ABC’??

This isn't getting me anywhere. Will appreciate any help or ideas??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-11 : 22:01:31

probably something like this ?

select view.name, view.item 
from   view
       inner join tableA on view.Col1 = tableA.t1 and view.Col2 = tableA.t2
       inner join tableB on view.Col1 = tableB.t5 and view.Col2 = tableB.t2
                                                  and view.item = 'ABC'
where  exists (select * from problemTable x where x.somecol = view.col2)

KH
[spoiler]Time is always against us[/spoiler]

its_me
Starting Member

6 Posts

Posted - 2012-09-11 : 22:22:12

Thanks KH!

If there is a match, I want to return YES in output.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-11 : 22:51:58

[code]select view.name, view.item, case when problemTable.anothercol = 'some value' then 'YES' else 'NO' end
from view
inner join tableA on view.Col1 = tableA.t1 and view.Col2 = tableA.t2
inner join tableB on view.Col1 = tableB.t5 and view.Col2 = tableB.t2
and view.item = 'ABC'
LEFT JOIN problemTable on view.col2 = problemTable.somecol[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources