February 16, 2006 at 2:06 pm
Hi all,
The query t1 returns 1 record: SELECT EMPLOYID
FROM UPR30100
WHERE EMPLOYID = '999999999' AND CHEKDATE = '2/14/2006'
The query t2 returns 3 records
SELECT EMPLOYID, TRXBEGDT, TRXENDDT
FROM UPR30300
WHERE EMPLOYID = '999999999' AND CHEKDATE = '2/14/2006'
result:
EMPLOYID TRXBEGDT TRXENDDT
999999999 1/31/2006 2/6/2006
999999999 1/31/2006 2/6/2006
999999999 1/31/2006 2/6/2006
The query in following returns 3 records :
ELECT u2.EMPLOYID, u1.TRXBEGDT, u1.TRXENDDT
FROM UPR30300 u1 INNER JOIN
UPR30100 u2 ON u1.EMPLOYID = u2.EMPLOYID AND u1.CHEKDATE = u2.CHEKDATE
WHERE (u1.EMPLOYID = '999999999') AND (u2.CHEKDATE = '2/14/2006')
How to join 2 queries to return only single record 😕
result should be:
EMPLOYID TRXBEGDT TRXENDDT
999999999 1/31/2006 2/6/2006
February 16, 2006 at 2:14 pm
SELECT distinct u2.EMPLOYID, u1.TRXBEGDT, u1.TRXENDDT FROM .....................................
Ideally the columns TRXBEGDT, TRXENDDT needed to be in UPR30100...normalization
-Krishnan
February 16, 2006 at 2:17 pm
Each row is the same?
Try the DISTINCT statement with the GROUP BY clause. Also change the WHERE clause into a HAVING clause:
SELECT DISTINCT u2.EMPLOYID, u1.TRXBEGDT, u1.TRXENDDT
FROM UPR30300 u1 INNER JOIN
UPR30100 u2 ON u1.EMPLOYID = u2.EMPLOYID AND u1.CHEKDATE = u2.CHEKDATE
GROUP BY u2.EMPLOYID, u1.TRXBEGDT, u1.TRXENDDT
HAVING (u1.EMPLOYID = '999999999') AND (u2.CHEKDATE = '2/14/2006')
February 16, 2006 at 2:36 pm
HAVING to be used only with aggregated values.
WHERE is right in this case.
Distinct is redundant if you are using GROUP BY.
SELECT u2.EMPLOYID, u1.TRXBEGDT, u1.TRXENDDT
FROM UPR30300 u1 INNER JOIN
UPR30100 u2 ON u1.EMPLOYID = u2.EMPLOYID AND u1.CHEKDATE = u2.CHEKDATE
WHERE (u1.EMPLOYID = '999999999') AND (u2.CHEKDATE = '2/14/2006')
GROUP BY u2.EMPLOYID, u1.TRXBEGDT, u1.TRXENDDT
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply