December 30, 2008 at 2:08 am
talking bout joins is there a way to join these two result sets :
TABLE 1
DATE
01/01/2008
02/01/2008
03/01/2008
TABLE 2
DATE EMP
01/01/2008 123
02/01/2008 123
03/01/2008 456
into these
RESULTS:
DATE EMP
01/01/2008 123
02/01/2008 123
03/01/2008 NULL
when I tried to use joins and with condition of Emp='123', it ommits the 3rd record in the 1st table (03/01/2008)
CURRENT RESULTS:
DATE EMP
01/01/2008 123
02/01/2008 123
thanks...
December 30, 2008 at 2:14 am
Hi,
you can write your condition in JOIN statement instead of WHERE statment.
table1 LEFT OUTER JOIN table2 ON table1.DATE = table2.DATE and table2.emp = '123'
This way it will select all records from table1 and matching join condition from table2. is it what you want?
Regards,
Nitin
December 30, 2008 at 2:20 am
Well you could join on the date columns and use CASE in the select query so that for 03/01/2008 emp is null.
Thing is why do u want such a result set.
SELECT T1.Date,
CASE WHEN EMP = 123 THEN EMP ELSE NULL END AS EMP
FROM Table1 T1 INNER JOIN Table2 T2 ON T1.date = T2.date
As i said iam preplexed why u want it like this...
"Keep Trying"
December 30, 2008 at 2:21 am
I don't know what is your code but you can look here that everything is OK!
CREATE TABLE TAB2
(
DATE DATETIME,
EMP INT
);
CREATE TABLE TAB1
(
DATE DATETIME
);
INSERT INTO TAB2
SELECT '01/01/2008', 123
UNION ALL
SELECT '02/01/2008', 123
UNION ALL
SELECT '03/01/2008', 456
INSERT INTO TAB1
SELECT '01/01/2008'
UNION ALL
SELECT '02/01/2008'
UNION ALL
SELECT '03/01/2008'
SELECT T1.DATE, T2.DATE, T2.EMP
FROM TAB1 T1 INNER JOIN TAB2 T2
ON T1.DATE = T2.DATE
WHERE T2.EMP= 123
Results are:
DATE DATE EMP
----------------------- ----------------------- -----------
2008-01-01 00:00:00.000 2008-01-01 00:00:00.000 123
2008-01-02 00:00:00.000 2008-01-02 00:00:00.000 123
(2 row(s) affected)
:hehe:
December 30, 2008 at 2:37 am
hehe..
i guess that solves my problem..
thanks to you guys..
December 30, 2008 at 2:53 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply