Select query INNER JOIN issue

Question

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

Aaron Dietz · Accepted Answer · 2016-08-19 17:36:24Z

2

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

edited Aug 19, 2016 at 17:36

answered Aug 19, 2016 at 17:34

Aaron Dietz

10.3k1 gold badge17 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheGameiswar · Accepted Answer · 2016-08-19 17:38:42Z

2

You also can use exists for your case

select 
* from requistions rq where exists(
select 1 from RequisitionDetails  rd where rd.RequisitionID=rq.RequisitionID
and rd.Quantity!=rd.ReceivedQuantity)

answered Aug 19, 2016 at 17:38

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Comments

dnoeth · Accepted Answer · 2016-08-19 17:40:57Z

2

As you don't need columns from the 2nd table you can also switch to EXISTS to avoid DISTINCT:

SELECT req.*
FROM dbo.Requisitions as req
WHERE EXISTS
 ( SELECT * FROM dbo.RequisitionDetails as req_det
    WHERE req.RequisitionID = req_det.RequisitionID
      AND Quantity <> ReceivedQuantity
 )

Or IN:

SELECT req.*
FROM dbo.Requisitions
WHERE RequisitionID IN
 ( SELECT RequisitionID
   FROM dbo.RequisitionDetails
    WHERE Quantity <> ReceivedQuantity
 )

answered Aug 19, 2016 at 17:40

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Collectives™ on Stack Overflow

Select query INNER JOIN issue

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related