Help diagnose bizzare MySQL query behavior

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 whic开发者_C百科h 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

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.

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).