Select column from table 2 if not exist on table 1

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

General SQL Server Forums

New to SQL Server Programming

Select column from table 2 if not exist on table 1

Author

Topic

emyk
Yak Posting Veteran

57 Posts

Posted - 2014-10-16 : 08:51:51

I am trying to pull a min value of a date column from table 1, if table 1 does not have any record I need pull a date column from table 2. Table 2 will always have a unique record ID (No duplicate ID's).

Table 1


ID date
1  1/1/2014
1  1/5/2014

Table 2


ID date
1  1/5/2014
2  10/15/2014

Here is the desired result when running for ID = 1 (select Min(date) where ID = 1, I should be getting 1/1/2014 from Table 1.
if i am running the same query where ID=2 then I should be getting 10/15/2014 from Table 2. So basically I need to check if a value exists on Table 1 first, if there is no value on Table 1 and then to grab the value from Table 2.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-16 : 09:24:16

[code]
DECLARE @table1 TABLE (id INT, DATE DATETIME)
DECLARE @table2 TABLE (id INT, DATE DATETIME)

INSERT INTO @table1 (id, DATE)
SELECT 1, '1/1/2014'

UNION ALL

SELECT 1, '1/5/2014'

INSERT INTO @table2 (id, DATE)
SELECT 1, '1/5/2014'

UNION ALL

SELECT 2, '10/15/2014'

SELECT t2.id, CASE
WHEN max(t1.id) IS NOT NULL
THEN Min(t1.DATE)
ELSE MAX(t2.DATE)
END AS mindate
FROM @table2 t2
LEFT JOIN @table1 t1 ON t2.id = t1.id
GROUP BY T2.ID
[/code]

emyk
Yak Posting Veteran

57 Posts

Posted - 2014-10-16 : 13:51:42

Hi gbritton - this is returning two dates from the two tables. How do i make it return one date only (check table 1 first, if table one does not have it, then grab table 2 date column)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-16 : 14:25:06

That's what it does. table 2 has two rows in it so you should expect two rows out. For the first row, there is a matching row in table 1, so you get the table 1 value. For the second row, there is no matching row in table1 so you get the table2 value. At least, that's how I understood your question.

emyk
Yak Posting Veteran

57 Posts

Posted - 2014-10-16 : 14:28:05

That worked perfect!! I removed t2.id from my select to get to the one return,

thank you so much:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources