Ayman Ayman - 3 months ago 7
SQL Question

Select query INNER JOIN issue

I have tow tables

Requisitions
and
RequisitionDetails


Requisitions table

+---------------+-----------------+
| RequisitionID | RequisitionDate |
+---------------+-----------------+
| 1 | 2016-08-17 |
| 2 | 2016-08-18 |
| 3 | 2016-08-19 |
+---------------+-----------------+


RequisitionDetails table

+---------------------+---------------+--------+----------+------------------+
| RequisitionDetailID | RequisitionID | ItemID | Quantity | ReceivedQuantity |
+---------------------+---------------+--------+----------+------------------+
| 1 | 1 | 1 | 2 | 1 |
| 2 | 1 | 2 | 3 | 2 |
| 3 | 2 | 3 | 4 | 3 |
+---------------------+---------------+--------+----------+------------------+


I am trying to get Requisition data where Quantity is not equal to ReceivedQuantity.

i have tried the below query but its record with RequisitionID
1
twice.

How can i make the query returns the Requisition data without repeating the requisition data based on items that have Quantity is not equal to ReceivedQuantity.

SELECT
dbo.Requisitions.RequisitionID,
dbo.Requisitions.RequisitionDate
FROM dbo.Requisitions
INNER JOIN dbo.RequisitionDetails
ON dbo.Requisitions.RequisitionID = dbo.RequisitionDetails.RequisitionID
where dbo.RequisitionDetails.Quantity != dbo.RequisitionDetails.ReceivedQuantity

Answer

It's returning twice because of the two rows with RequistionID = 1 in the RequistionDetails table. Since the rows returned are exact duplicates you can simply add the DISTINCT keyword to your select to see one of them:

SELECT DISTINCT
dbo.Requisitions.RequisitionID,
dbo.Requisitions.RequisitionDate
FROM dbo.Requisitions
INNER JOIN dbo.RequisitionDetails
ON dbo.Requisitions.RequisitionID = dbo.RequisitionDetails.RequisitionID
where   dbo.RequisitionDetails.Quantity!=
dbo.RequisitionDetails.ReceivedQuantity

You should also use some aliases to clean up your query:

SELECT DISTINCT
           R.RequisitionID,
           R.RequisitionDate
FROM       dbo.Requisitions R
INNER JOIN dbo.RequisitionDetails RD ON R.RequisitionID = RD.RequisitionID
WHERE      RD.Quantity != RD.ReceivedQuantity