Table preference in query logic

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 2000 Forums

SQL Server Development (2000)

Table preference in query logic

Author

Topic

rlull
Starting Member

39 Posts

Posted - 2006-09-21 : 11:37:00

I am retrieving data from two tables. Both contain date fields. If both tables contain a row that have the same date, I want Table 1 to have preference so that only a single record is returned for this date. Is there a way to do this via a single query?

Kristen
Test

22859 Posts

Posted - 2006-09-21 : 11:42:47

Like this perhaps?

SELECT *
FROM Table1
UNION ALL
SELECT *
FROM Table2 AS T2
WHERE NOT EXISTS(SELECT * FROM Table1 AS T1 WHERE T1.MyDate = T2.MyDate)

Kristen

rlull
Starting Member

39 Posts

Posted - 2006-09-21 : 12:08:12

Excellent, thanks!

rlull
Starting Member

39 Posts

Posted - 2006-09-21 : 12:43:31

Ok, now to take this one step further, is there a way to accomplish this same task without using a UNION? My column names/types don't match up as required.

Kristen
Test

22859 Posts

Posted - 2006-09-21 : 14:21:00

No, you need the UNION, but you could do a variation on this, rather ridiculous, example


SELECT MyPK1_1, Col1_1, Col1_2, NULL, NULL
FROM Table1
UNION ALL
SELECT MyPK2_1, NULL, NULL, Col2_1, Col2_2
FROM Table2 AS T2
WHERE NOT EXISTS(SELECT * FROM Table1 AS T1 WHERE T1.MyDate = T2.MyDate)

i.e. use the same output column position for columns that are equivalent and "unique" columns for things that only occur in one of the tables - and output NULL in that position from the other table

Kristen

rlull
Starting Member

39 Posts

Posted - 2006-09-21 : 14:45:09

Thanks, again. I think that will work.

Rick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources