SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: help with query

  1. #1
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help with query

    i need to pull all transactions where ttype=18080 OR ttype=10091 but !=10105.

    Code:
    $sql = "SELECT transactions.tnumber, transactions.tdate, transactions.shift, transactions.location, transactions.terminal,  tvalues.ttype
            FROM transactions
            JOIN tvalues 
    		     ON tvalues.transid2=transactions.transid
    	   WHERE transactions.tdate >= '$StartDate'
                 AND transactions.tdate <= '$EndDate' 
    			 AND (transactions.terminal ='1' OR transactions.terminal ='2')
    			 AND tvalues.ttype !='10105'
    			 AND (tvalues.ttype ='18080' OR tvalues.ttype ='10091')
    			 AND transactions.location='$loc'
    	   ORDER by transactions.location
    	   ";
    each transaction can have multiple ttype. so if a transaction contains tvalues.ttype 18080 and 10105, then i don't want that transaction to be displayed only transactions that contain ttype 18080 OR 10091. the above query still displays the transaction because it has ttype 18080 in there along with 10105.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,492
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    You'll have to use a subquery that selects all transid2 from tvalues with ttype = '10105', left join the result to your query, and add a WHERE condition to select only the rows that are not present in the subquery:
    Code:
    SELECT 
        transactions.tnumber
      , transactions.tdate
      , transactions.shift
      , transactions.location
      , transactions.terminal
      , tvalues.ttype
    FROM transactions
    JOIN tvalues 
    ON tvalues.transid2=transactions.transid
    LEFT OUTER JOIN
      (SELECT transid2
       FROM tvalues
       WHERE ttype = '18080'
      ) AS tv2
    ON tv2.transid = transactions.transid
    WHERE transactions.tdate >= '$StartDate'
    AND transactions.tdate <= '$EndDate' 
    AND (transactions.terminal ='1' OR transactions.terminal ='2')
    AND tv2.transid2 IS NULL
    AND tvalues.ttype IN ('18080', '10091')
    AND transactions.location='$loc'
    ORDER by transactions.location

  3. #3
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ahhh...thanks for the quick reply...it works!


Bookmarks

Posting Permissions

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