sub 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 2008 Forums

Transact-SQL (2008)

sub query

Author

Topic

andydexster
Starting Member

7 Posts

Posted - 2010-05-24 : 15:05:26

I have a code using except function
can any one convert the code using sub queries?
I am using 2 databases and 2 different tables

here is the code:

SELECT [MODE],[NUMBER],[DESCRIPTION],[UNITSIZE]
FROM [database1].[DBO].[table1]
EXCEPT
SELECT [_ZONE],[MODE],[NUMBER],[DESCRIPTION]
FROM [database2].[DBO].[table2]
WHERE [ENTRYDATE] = ( SELECT MAX (ENTRYDATE) FROM [database2] . [DBO] . [table2] )

andy

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-25 : 00:03:29

Try it:

SELECT [MODE],[NUMBER],[DESCRIPTION],[UNITSIZE]
FROM [database1].[DBO].[table1] t1
where not exists
(SELECT 1 FROM [database2].[DBO].[table2] T2
WHERE T1.[MODE] = T2.[_ZONE] AND T1.[NUMBER] = T2.[MODE] AND T1.[DESCRIPTION] = T2.[NUMBER]
AND T1.[UNITSIZE] = T2.[DESCRIPTION] AND T2.[ENTRYDATE] =
( SELECT MAX (ENTRYDATE) FROM [database2] . [DBO] . [table2] T3 ))

One strange thing i noticed in your except query that you are comparing number with mode and not number, description with number and not description and so on..

Regards,
Bohra

andydexster
Starting Member

7 Posts

Posted - 2010-05-25 : 11:40:33

actually sorry for for the wrong code to type actual code is like this:

SELECT [_ZONE],[MODE],[NUMBER],[DESCRIPTION],[UNITSIZE]
FROM [database1].[DBO].[table1]
EXCEPT
SELECT [_ZONE],[MODE],[NUMBER],[DESCRIPTION],[UNITSIZE]
FROM [database2].[DBO].[table2]
WHERE [ENTRYDATE] = ( SELECT MAX (ENTRYDATE) FROM [database2] . [DBO] . [table2] )

I have actully 28 columns ..i just type 5 of them ...so is there any other way to write it

andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources