select statement - 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)

select statement

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-23 : 11:21:12

Hi,

Hi,

I have a temp table with a columns such as bu_id,salesrep_id,billto_id

I have 3 other lookup tables:
businessunit table with bu_id,bufile_id
salesrep table with salesrep_id, salesrep_fileid
billto table with billto_id and billtofile_id

The values that are inputted into the temp table are the fileids

I need to run through the table and check if the values in the temp table exist in the lookup tables and if not raise an error.
Is it possible to have one select statement for the 3 fields as there is no direct relationship between them?

This is what i have now

set @XMLResult=(SELECT ColorCommaDelimitedList = 
Stuff((SELECT DISTINCT ', ' + businessunit_id AS [text()] 
        FROM   
        (select businessunit_id from TEMP_DDPL2 where businessunit_id not in (select businessunit_fileID 
from BusinessUnit)) x
        For XML Path ('')),1,1,'') )
if len(@XMLResult)>0 begin
RAISERROR('Business unit not found: %s ',16,1,@XMLResult)
 
		RETURN
end

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 12:45:12

nope. if you need three lists you need separate selects

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-24 : 12:54:50

Thanks thought so too but wanted to make sure :-)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 13:21:20

or you can use a main select but inside that you need three subqueries with select for each

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources