Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2011
    Posts
    29

    Unanswered: Help with IN to JOIN migration

    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.
    Last edited by RGM; 08-02-11 at 02:13.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2011
    Posts
    29
    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).
    Last edited by RGM; 08-01-11 at 23:49.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RGM View Post
    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 View Post
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2011
    Posts
    29
    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!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •