I have this query that return 2 rows
SELECT DISTINCT
ah.entryNo,
ah.empID,
ah.empName,
ah.department,
ah.date,
ad.quantity
FROM
accountabilityHeader AS ah
INNER JOIN
accountabilityDetails AS ad ON ah.entryNo = ad.entryNo
WHERE
ad.assetCode = '24IT001'
AND ad.status = 'ACTIVE'
OUTPUT WITHOUT SECOND JOIN:
| Entry No | empID | empName | department | date | quantity |
|---|---|---|---|---|---|
| 9 | 6033 | TEST | IT | 2024-07-22 | 1 |
| 10 | NULL | NULL | IT | 2024-07-22 | 1 |
Now if i add my new JOIN and add the ms.assetSerial,ms.usedBy, in SELECT
INNER JOIN
masterfileStock AS ms ON ad.assetCode = ms.assetCode
OUTPUT AFTER ADDING THE NEW JOIN:
| Entry No | empID | empName | department | date | quantity | assetSerial | userBy |
|---|---|---|---|---|---|---|---|
| 9 | 6033 | TEST | IT | 2024-07-22 | 1 | 24IT001-01 | 6033 |
| 9 | 6033 | TEST | IT | 2024-07-22 | 1 | 24IT001-02 | IT |
| 10 | NULL | NULL | IT | 2024-07-22 | 1 | 24IT001-01 | 6033 |
| 10 | NULL | NULL | IT | 2024-07-22 | 1 | 24IT001-02 | IT |
My target output must be:
| Entry No | empID | empName | department | date | quantity | assetSerial | userBy |
|---|---|---|---|---|---|---|---|
| 9 | 6033 | TEST | IT | 2024-07-22 | 1 | 24IT001-01 | 6033 |
| 10 | NULL | NULL | IT | 2024-07-22 | 1 | 24IT001-02 | IT |