help to 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 to query

Author

Topic

ibin
Starting Member

26 Posts

Posted - 2009-10-13 : 08:13:11

i have three tables as stated below:

and i want the final result to contain linkid ,date ,LookupID
given a LinkID and Desc

Note: desc can be null also..
CREATE TABLE #Temp (
LinkID varchar(6),
Desc varchar(6),
date datetime
)
INSERT INTO #Temp (LinkID , Desc, date) VALUES ('ABC','test1','1/1/2009')
INSERT INTO #Temp (LinkID , Desc, date) VALUES ('DEF','test2','2/1/2009')
INSERT INTO #Temp (LinkID , Desc, date) VALUES ('HIG','','3/1/2009')

CREATE TABLE #Lookup(
ID varchar(6),
Desc nvarchar(max))
INSERT INTO #Lookup(ID , Desc) VALUES ('a1','test1')
INSERT INTO #Lookup(ID , Desc) VALUES ('a2','test2')
INSERT INTO #Lookup(ID , Desc) VALUES ('a3','test3')

CREATE TABLE #lookupMain (
mainid varchar(6),
LinkID varchar(6),
LookupID varchar(6)
)
INSERT INTO #Main (mainid ,LinkID , LookupID ) VALUES ('123','ABC','a1')
INSERT INTO #Main (mainid ,LinkID , LookupID ) VALUES ('124','DEF','a2')
INSERT INTO #Main (mainid ,LinkID , LookupID ) VALUES ('126','HIG','a3')
INSERT INTO #Main (mainid ,LinkID , LookupID ) VALUES ('125','ABC','')

eralper
Yak Posting Veteran

66 Posts

Posted - 2009-10-13 : 09:30:23

Hello ibin,

Please review and test the below t-sql select query


select
	#Temp.linkid ,#Temp.[date] ,#Main.LookupID
from #Temp
inner join #Main on #Temp.LinkID = #Main.LinkID
inner join #Lookup on #Lookup.ID = #Main.LookupID
where #Temp.LinkID = 'ABC' and #Temp.[Desc] = 'test1'

-------------
Eralper
http://www.kodyaz.com

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-10-13 : 09:42:55

small modification to eralper query
declare @linkid varchar(32), @desc varchar(32)
select @linkid = 'abc', @desc = null
select
#Temp.linkid ,#Temp.[date] ,#Main.LookupID
from #Temp
inner join #Main on #Temp.LinkID = #Main.LinkID
inner join #Lookup on #Lookup.ID = #Main.LookupID
where #Temp.LinkID = @linkid and (@desc is null or #Temp.[Desc] = @desc)

ibin
Starting Member

26 Posts

Posted - 2009-10-14 : 01:06:16

Thanks... with some modification got the solution....:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources