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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with SQL query

Author

Topic

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-15 : 03:04:09

hi,

i do not know what subject this sql should falls under but anyway here's the confusing part:

get distinct CompanyID from T2 and if T1 has CompanyID of T2, to get that CompanyID from T1.
If CompanyID from T3 exist in T1, to get only CompanyID & companyName that exist and not the whole table

T1 & T2
CompanyID

T3
ID
CompanyID
CompanyName

select distinct a.CompanyId, u.CompanyName from T1 a,T3 u where a.CompanyID=u.CompanyID

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-15 : 03:08:47

select distinct t1.companyid , t3.companyname
from t1 as t1
inner join t2 as t2 on t1.companyid = t2.companyid
inner join t3 as t3on t1.companyid = t3.companyid

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-15 : 03:22:22

Thanks Thks :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-15 : 03:32:03

quote:
Originally posted by melon.melon

hi,
get distinct CompanyID from T2 and if T1 has CompanyID of T2, to get that CompanyID from T1.
If CompanyID from T3 exist in T1, to get only CompanyID & companyName that exist and not the whole table

How about the ELSE part ?
if T1 does not have CompanyID of T2 ?
if CompanyID from T3 NOT exist in T1 ?

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

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-15 : 04:14:14

hmm...need only one query to cover the scenerio above, forgive me i know i am being un-concise when its confused :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-15 : 04:26:10

i mean what do you want for the ELSE part.

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

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-15 : 04:39:24

i'm doing this for a dropdownlist with a Button to populate data in various fields so i need to make sure T2 (fixed set of CompanyID) is populated in dropdownlist and T1 is the populated data. T3 has all the CompanyID but only CompanyName is needed to display the text in the dropdownlist.

Does the above query match exactly with my application above ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-15 : 04:42:52

sounds like you need this


select t.CompanyID, t3.CompanyName
from   (
           select CompanyID from t1 union
           select CompanyID from t2
       ) t
       inner join t3

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

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-15 : 05:05:57

Thanks for your nice suggestion lol
But ur query doesnt work.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-15 : 05:07:50

quote:
Originally posted by melon.melon

Thanks for your nice suggestion lol
But ur query doesnt work.

yes. of-course it doesn't work. I missed out the JOIN condition



select t.CompanyID, t3.CompanyName
from   (
           select CompanyID from t1 union
           select CompanyID from t2
       ) t
       inner join t3 ON t.CompanyID = t3.CompanyID

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

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-15 : 06:05:45

It works but it returns all the company id in T1 & T2...which has replicated companyid.
Companyid in T2 = T1
but
there are replication of company id in T1 so count(T1.CompanyID) > count(T2.CompanyID)
bklr query looks good because my dropdownlist can display T2.CompanyID with CompanyName as a text display
and picks T1.CompanyID to populate field.

Anyway, thanks for looking into this. Appreciate it =)

By the way,

if distinct is added, it will returned CompanyID & CompanyName from t3 which exist in t1 & t2:
select distinct t.CompanyID, t3.CompanyName
from (
select CompanyID from t1 union
select CompanyID from t2
) t
inner join t3 ON t.CompanyID = t3.CompanyID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources