cross join or what?? - 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)

cross join or what??

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-06-22 : 00:55:49

I have to fetch data from two tables (clocking and pdo)
clocking: eid,cl_Date,timeIn,timeOut,hour
pdo: eid,pdo_date,pdo_hours

I have to fetch eid, cl_date, timein, timeout, hour, pdo_date, pdo_hours.

I am doing following and it should give me 18 records from clocking table and 2 records from pdo table. clocking contains employee time in and time out (usually 2 recods per day cause lunch out/lunch in). pdo is where employee took a paid day off. I should be getting 20 recods instead I am getting 36 records (double of clocking table) and every records has data from pdo table. I need the data from pdo table in new records. (exactly like union but notice columns r diff in these table)

SELECT DISTINCT
clocking.eid, CLOCKING.cl_date, pdo.pdo_hours, pdo.pdo_hours,
clocking.hours
FROM clocking cross JOIN
pdo ON clocking.eid = pdo.eid
WHERE (clocking.eid = 3321) AND (pdo.eid = 29) AND (clocking.cl_date BETWEEN
CONVERT(DATETIME, '2008-12-01 00:00:00', 102) AND CONVERT(DATETIME, '2008-12-15 00:00:00', 102)) AND
(pdo.pdo_date BETWEEN CONVERT(DATETIME, '2008-12-01 00:00:00', 102) AND CONVERT(DATETIME, '2008-12-15 00:00:00', 102))

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2009-06-22 : 01:21:48

hmmm - Cross joins.....

Forget about them - you need a UNION for this query
Something like this:

SELECT eid, cl_date As WorkingDay, timeout - timein as WorkedHours, 0 as PaidDayOff FROM Clocking
UNION
SELECT eid, pdo_date as WorkingDay, pdo_hours as WorkedHours, 1 as PaidDayOff FROM pdo

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-06-22 : 01:45:50

hi timmy,

thanks for your reply. I am with you. I end up using Union. Added some empty column to my query2 and bingo. I didnt know whether that was norm or may be someone else could offer a better solution rather band-air.

Again, thanks for your reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources