rai nalasa rai nalasa - 3 months ago 8
MySQL Question

Sql query returns row which was not specified by Where clause

My SQL query doesn't return an output that suppose to be specified by my where clause.

Here is my query:

SELECT
transaction_details.transaction_id
,transaction_details.transaction_number
,transaction_details.product_id
,Products3.ProductName
FROM transaction_details
INNER JOIN Products3
ON transaction_details.product_id = Products3.productID
INNER JOIN transaction_status
ON transaction_details.transaction_id = transaction_status.transaction_id
WHERE status_of_transaction = 'review'


This query should return me table rows with a
status_of_transaction = 'review'
.

Here is my table which contains the
status_of_transaction

enter image description here

I tried
DISTINCT
but its not working.

and this is the output I always get:

enter image description here

is there something wrong with my query its is returning table row which was not specified by my WHERE clause?

Answer

There are multiple rows generated by the set (remember this is all set theory) - you are missing a FK/PK relationship in the JOIN.

I suggest either picking it apart table by table, or using select * and look for the difference in the column.

SELECT 
    transaction_status.*
FROM transaction_details
INNER JOIN Products3
ON transaction_details.product_id = Products3.productID
INNER JOIN transaction_status
ON transaction_details.transaction_id = transaction_status.transaction_id
WHERE status_of_transaction = 'review'

I believe the issue is with the transaction_status join. The transaction_id is the same for both rows in transaction_details - which matches TWO rows in transaction_status. SQL will create 4 rows -- two that match "review" and two for "replied" (remove the WHERE to see them). This is a cross join of sorts.

Does the _status table also contain transaction_number? There needs to be a way to JOIN to the single row from _details to _status. Somehow that _status row belong to the _details row. Look at the PK/FK definitions for both tables.

Not knowing your schema - I would guess the solution is....

SELECT 
transaction_details.transaction_id
,transaction_details.transaction_number
,transaction_details.product_id
,Products3.ProductName
FROM transaction_details
INNER JOIN Products3
ON transaction_details.product_id = Products3.productID
INNER JOIN transaction_status
ON transaction_details.transaction_id = transaction_status.transaction_id
-- ADDED NEXT LINE
  AND transaction_details.transaction_number = transaction_status.transaction_number
-- END Change
WHERE status_of_transaction = 'review'