Query help - 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)

Query help

Author

Topic

shailesh18
Starting Member

8 Posts

Posted - 2011-11-01 : 07:01:06

Hi
Table 1


PID   SID
------------
1      1
1      2
1      3
1      4

Table 2


PID   SID    PlDate
------------------------
1      1     01/01/2011
1      2     02/01/2011

I would like to have an output like


PID   SID   PlDate
------------
1      1    01/01/2011
1      2    02/01/2011
1      3    NULL
1      4    NULL

How can i achieve this?
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 07:15:26

select t1.PID, t1.SID, t2.P1Date
from t1
left join t2
on t1.PID = t2.PID
and t1.SID = t2.SID

am I first?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shailesh18
Starting Member

8 Posts

Posted - 2011-11-01 : 07:41:49

Thanks
this will return only the common rows from table 1 and table 2.
I want the rows from both the table.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 07:47:23

It will return all rows fromt1 and matching rows from t2
For all rows from both tables

select PID = coalesce(t1.PID,t2.PID), SID = coalesce(t1.SID,t2.SID), t2.P1Date
from t1
full outer join t2
on t1.PID = t2.PID
and t1.SID = t2.SID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shailesh18
Starting Member

8 Posts

Posted - 2011-11-01 : 08:22:31

Thanks. It worked.
to simplify here an sql


CREATE TABLE #table1(
PID INT,
SID INT
)

INSERT INTO #table1 VALUES (1,1)
INSERT INTO #table1 VALUES (1,2)
INSERT INTO #table1 VALUES (1,3)
INSERT INTO #table1 VALUES (1,4)


CREATE TABLE #table2(
PID INT,
SID INT,
PlDate datetime,
Col1 INT
)

INSERT INTO #table2 VALUES (1,1,getdate(),1)
INSERT INTO #table2 VALUES (1,2,getdate(),1)


select PID = t1.PID, SID = t1.SID, t2.PlDate
from #table1 t1
full outer join #table2 t2
on t1.PID = t2.PID
and t1.SID = t2.SID


DROP TABLE #table1
DROP TABLE #table2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources