Inner Join Query - 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

Inner Join Query

Author

Topic

abuhassan

105 Posts

Posted - 2007-03-15 : 09:32:22

Hi

I have two tables and one linking table they look something like this

<<<<<<<Application Table>>>>>>

AppID AppName
1 MS Word
2 Excel
3 SoftGrid

<<<<<<System Table>>>>>>>>

SysID SysName
1 System 1
2 System 2
3 Server 1

<<<<<<Link Table>>>>>>>>>

SysID AppID
1 1
1 3

How can i retirve the data so that it picks up and displays all the applications that are not on the the server as well as those that are

for example the output im trying to get is as follows from the data above.......

SysName AppName Y/N
System 1 MS Word Y
System 1 Excel N
System 1 SoftGrid Y
System 2 MS Word N
System 2 Excel N
System 2 SoftGrid N
Server 1 MS Word N
Server 1 Excel N
Server 1 SoftGrid N

i want to able to get a dynamically generated Y/N colunm

Im ok with doing innerjoin queries but the problem is how do i get the rest of the results that have no links to show that there it does not exist.

Thanks

Andraax
Aged Yak Warrior

790 Posts

Posted - 2007-03-15 : 09:36:32

select s.sysname, a.appname,
case when exists (select 1 from linktable where sysid=s.sysid and appid=a.appid) then 'Y' else 'N' end as [Y/N]
from system s cross join application a

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 09:39:36

[code]select s.sysname,
a.appname,
case when l.sysid is null then 'N' else 'Y' end as [Y/N]
from system as s
cross join application as a
left join linktable as l on l.sysid = s.sysid and l.appid = a.appid[/code]

Peter Larsson
Helsingborg, Sweden

abuhassan

105 Posts

Posted - 2007-03-15 : 09:46:30

Thanks

Never used or come accross a cross join before where can i learn more about them? does any one have good links to it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources