Help diagnose bizzare MySQL query behavior

Question

Hi,

I have a very specific query that is acting up and I could use any help at all with debugging it.

There are 4 tables involved in this query.

Transaction_Type
Transaction_ID (primary)
Transaction_amount
Transaction_Type

Transaction
Transaction_ID (primary)
Timestamp

Purchase
Transaction_ID
Item_ID

Item
Item_ID
Client_ID

Lets say there is a transaction in which someone pays $20 in cash and $0 in credit it inserts two rows into the table.

//row 1
Transaction_ID: 1
Transaction_amount: 20.00
Transaction_type: cash
//row 2
Transaction_ID: 1
Transaction_amount: 0.00
Transaction_type: credit

here is the specific query:

SELECT 
 tt.Transaction_Amount, tt.Transaction_ID
FROM 
 ItemTracker_dbo.Transaction_Type tt
JOIN 
 ItemTracker_dbo.Transaction t
   ON
 tt.Transaction_ID = t.Transaction_ID
JOIN
 ItemTracker_dbo.Purchase p
   ON
 p.Transaction_ID = tt.Transaction_ID
JOIN
 ItemTracker_dbo.Item i
   ON
 i.Item_ID = p.Item_ID
WHERE
 t.TimeStamp >= "2010-01-06 00:00:00" AND t.TimeStamp <= "2010-01-06 23:59:59"
   AND
 tt.Transaction_Format IN ('cash', 'credit')
   AND
 i.Client_ID = 3

when I execute this query, it returns 4 rows for a specific transaction. (it should be 2)

When I remove ALL where clauses and insert WHERE tt.Transaction_ID = problematicID it only returns two.

EDIT:::::
still repeats upon changing date range The kicker:

*When I change the initial daterange it only returns two rows for that specific transaction_id.*
::::

Is it the way I use join? that's all I can think of...

EDIT: This is the problem

in purchase - two sepparate purchase_ID's can have the same transaction_ID (purhcase_ID breaks down specific item sales).

There are duplicate Transaction_ID rows in purchase_ID

Answer 1

We need to see all the data in all the tables to be able to know where the problem is. However, because the joins are the problem it is because one of your tables has two rows when you think it has only one.

Answer 2

There's a problem with your schema. You have rows with the same transaction_id, which is the primary key. I would think they couldn't be marked primary in that database. With two rows with the same id, that could cause unexpected extra rows to come back from the join(s).