Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    9

    Unanswered: SQL select statement issue

    I had the following SQL select statement:
    select t.placedelivery,d.itemdesc,t.qty,(d.casecost * t.qty)AS cost from trn_po p,trn_po_details d, trn_po_trailers t where d.poid=p.poid and t.poid=p.poid and d.[lineno]=t.[lineno] and podt>'2009-10-01' and EXISTS(Select * from trn_po_trailers where poid=p.poid and placedelivery='03999')

    The results I get as the following:
    placedelivery itemdesc qty cost
    ------------- ------------ ----------------- ----------------
    03999 RAMBUTAN 7.0000 137.0600
    03005 RAMBUTAN 6.0000 117.4800

    However, what I expected is only data for 03999 is display but not 03005 to be display also.

    Any idea on how to modified my sql?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    select t.placedelivery,
    	d.itemdesc,
    	t.qty,
    	(d.casecost * t.qty) AS cost 
    from trn_po p
    	INNER JOIN trn_po_details d ON
    		p.poid = d.poid
    	INNER JOIN trn_po_trailers t ON
    		d.poid = t.poid AND
    		d.[lineno] = t.[lineno] and 
    where 
    	podt > '2009-10-01' and 
    	EXISTS	(Select * 
    			from trn_po_trailers 
    			where poid=p.poid and 
    				placedelivery='03999'
    			)
    You select all deliveries of all trailers that have at least once visited the PlaceDelivery '03999'.

    If you only want deliveries to PlaceDelivery '03999', replace the WHERE by:
    Code:
    where 
    	podt > '2009-10-01' and 
    	placedelivery='03999'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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