[Mysql]: select UNIQUE w/ join

Question

I have a series of related tables.

Transaction_Type
Transaction_ID (primary)
Transaction_amount
Transaction_Type

Transaction
Transaction_ID (primary)
Timestamp

Purchase
Transaction_ID
Item_ID Purchase_ID (primary)

Item
Item_ID
Client_ID

I need to select transaction_type rows based on a time stamp and client_id

My query is as follows:

SELECT 
    SUM(tt.Transaction_Amount)
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 >= $start AND t.TimeStamp <= $end
   AND
    tt.Transaction_Format IN ('cash', 'credit')
   AND
    i.Client_ID = $client_ID

The issue:

If a customer purchases more than one item in a transaction - a transaction_id will have multiple rows in the Purchase table. So the join is repeating the transaction_amount for transaction_id's that have multiple rows in purchase.

Is there a way to apply UNIQUE to the rows in purchase for this query?

Is there any other way to get around the multiple rows in purchase?

Or do I need to re-evaluate the architecture of my database?

If any additional info would be helpful please let me know.

Answer 1

I think that you have to use subquery - one with function sum and nested query with DISTINCT

SELECT SUM(Transaction_Amount) 
FROM (
   SELECT DISTINCT
       tt.Transaction_Amount, t.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 >= $start AND t.TimeStamp <= $end
      AND
       tt.Transaction_Format IN ('cash', 'credit')
      AND
       i.Client_ID = $client_ID
) table

I hope that I understood your question. If no then sorry for misunderstanding

Answer 2

you can group your results by Transaction_ID and Transaction_amount and then take the global sum of that.

select sum(Transaction_amount) from
(select Transaction_ID, Transaction_amount
from [...]
group by Transaction_ID, Transaction_amount) a

Answer 3

Use a sub query.

SELECT SUM(tt.Transaction_Amount)
FROM ItemTracker_dbo.Transaction_Type tt
WHERE tt.Transaction_ID IN 
(SELECT t.Transaction_ID FROM
        ItemTracker_dbo.Transaction t
    JOIN
        ItemTracker_dbo.Purchase p
       ON
        p.Transaction_ID = t.Transaction_ID
    JOIN
        ItemTracker_dbo.Item i
       ON
        i.Item_ID = p.Item_ID
    WHERE
        t.TimeStamp >= $start AND t.TimeStamp <= $end
       AND
        tt.Transaction_Format IN ('cash', 'credit')
       AND
        i.Client_ID = $client_ID)

Answer 4

Need to use inner select, something like this:

select sum(tt.Transaction_Amount)
from ItemTracker_dbo.Transaction_Type tt
inner join ItemTracker_dbo.Transaction t ON tt.Transaction_ID = t.Transaction_ID
inner join
(select distinct(transaction_id) as transID
from Purchase as P
inner join Item as I on P.Item_ID = I.Item_ID
where I.Client_ID = $client_ID) blah
on t.Transaction_ID = blah.transID
t.TimeStamp >= $start AND t.TimeStamp <= $end
   AND
    tt.Transaction_Format IN ('cash', 'credit')