Help with IN to JOIN migration

RGM · #1 (**permalink**) 08-01-11, 22:26

Had this query licked until the specs changed.. Now I'm a bit stuck.

In words:
I need to find transactions in a given range that have a certain payment method (VHR RED).
For those, I need to get the transaction data and any other items they might have.

Best I could come up with was to get the transaction ID's that fell in the range & had a 'VHR RED' in them.

Then use ^ inside an IN() with a second query.

The query to get the ids list:

Code:

SELECT GROUP_CONCAT(t.id) as ids
FROM transactions t
LEFT JOIN transactions_items ti 
    ON ti.transaction_id = t.id
WHERE d.date_added
BETWEEN ".$values['start_date']."
    AND "'.$values['end_date'].'"
    AND ti.pay_method = "VHR RED"
ORDER BY t.date_added DESC

And the query to get the 'real' required data

Code:

SELECT t.dps as "DPS"
            , ti.amount as "Inclusive Amount"
	    , DATE_FORMAT(t.date_added, "%d/%c/%Y %T") as "Transaction Date"
	    , DATE_FORMAT(v.expiry_date, "%d/%c/%Y %T") as "Expiry Date"
	    , v.hash as "Voucher Code"
	    , ti.pay_method as "Payment Type"
FROM transactions t
LEFT JOIN transaction_items ti
    ON ti.transaction_id = t.id 
LEFT JOIN vouchers v
    ON v.id = ti.voucher_id
WHERE t.id
    IN ('.$rows['ids'].')
ORDER BY d.date_added DESC

Left a little bit of application code in there for dates and the id csv.
It does work fine but from what I've read about IN() it seems to be avoided if possible.

I figure the first query should be a sub select in a join but can't quite get there...

Thanks for any help.

r937 · #2 (**permalink**) 08-01-11, 22:39

this may take a couple of steps but we'll crack it eventually

first of all, since you have the condition AND ti.pay_method = "VHR RED", you are explicitly ruling out any possibility of ti.pay_method being NULL, so what you want is an INNER JOIN, not a LEFT OUTER JOIN

correct?

r937 · #3 (**permalink**) 08-01-11, 22:42

also, can a transaction have multiple transaction items? do all of them have to have the VHR RED payment method,or just one of them? can there be more than one VHR RED transaction item for a transaction?

RGM · #4 (**permalink**) 08-01-11, 22:43

Correct. It'll never be NULL but I do need to (eventually) find the other types the transaction may have.

Sorry your second reply wasn't there when I replied.

A Transaction can have multiple items.
At least one has to be VHR RED
There can be more than one VHR RED

I need to find the ones that have one or more VHR RED's in them and for those, get all the items that it has (including any/all VHR RED types).

r937 · #5 (**permalink**) 08-01-11, 23:46

Quote:

Originally Posted by RGM

I need to find the ones that have one or more VHR RED's in them ...

that part makes sense

Quote:

Originally Posted by RGM

... and for those, get all the items that it has (including any/all VHR RED types).

that part doesn't

why does your second query, the one that gets the "real" required data, not return the transaction items?

instead, it joins to donation_items, a different table

RGM · #6 (**permalink**) 08-02-11, 01:15

Error: donation_items = transaction items. I've edited the table.

The system is a donations thing but I've renamed it here to transactions as I thought it would make more sense to people to have 'shopping' in their heads... apparently not so when I mix the two!

r937 · #7 (**permalink**) 08-02-11, 16:02

Code:

SELECT t.dps AS "DPS"
     , ti.amount AS "Inclusive Amount"
     , DATE_FORMAT(t.date_added, "%d/%c/%Y %T") AS "Transaction Date"
     , DATE_FORMAT(v.expiry_date, "%d/%c/%Y %T") AS "Expiry Date"
     , v.hash AS "Voucher Code"
     , ti.pay_method AS "Payment Type"
  FROM transactions t
INNER
  JOIN transaction_items ti
    ON ti.transaction_id = t.id 
INNER
  JOIN vouchers v
    ON v.id = ti.voucher_id
 WHERE t.date_added BETWEEN ".$values['start_date']."
                        AND "'.$values['end_date'].'"
   AND EXISTS
      ( SELECT 1 
          FROM transaction_items
         WHERE transaction_id = t.id 
           AND pay_method = 'VHR RED' )
ORDER 
    BY t.date_added DESC

let me know if there's any part that you don't understand

Internet Services

Web Development

Digital Marketing

Consumer Tech