1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query

Discussion in 'SQL PL/SQL' started by sunny1234, Mar 26, 2015.

  1. sunny1234

    sunny1234 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    I have a customer table and transaction table. I want to display all the customer id from Transaction table only if the ShipmentStatus ='Yes'. If any record for ShipmentStatus = NO, I want to exclude all the records for that customer ID. How do i write a query to search in this case?

    Customer table:
    Customer ID, Order date, Item
    1234
    2345
    6677

    Transaction Table:
    Customer ID, Transaction ID, ShipmentStatus
    1234 45566 Yes
    1234 78787 No
    1234 55788 Yes
    6677 46777 Yes
    6677 54666 Yes
     
  2. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Code (SQL):

    SELECT t.*
    FROM transactions t
    WHERE NOT EXISTS(SELECT 1
                           FROM transactions t2
                           WHERE t.customer_id = t2.customer_id
                              AND t2.shipmentstatus = 'NO' )
     
     
  3. sunny1234

    sunny1234 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Thanks eras. The above did not work. I want to display the customers ids only if there, all shipments status ='Yes' if any of the transactions are No thn exclude the customer id.
    In the above data sample. Only 6677 customer id should be displayed.
     
  4. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    sunny1234,

    can You just replace 'NO' with 'No' ?

    Code (SQL):

    SELECT t.*
    FROM transactions t
    WHERE NOT EXISTS(SELECT 1
                           FROM transactions t2
                           WHERE t.customer_id = t2.customer_id
                              AND t2.shipmentstatus = 'No' )
     
    or if You want to display ONLY customers id (in Your case only 6677) then:
    Code (SQL):

    SELECT DISTINCT(t.customer_id)
    FROM transactions t
    WHERE NOT EXISTS(SELECT 1
                           FROM transactions t2
                           WHERE t.customer_id = t2.customer_id
                              AND t2.shipmentstatus = 'No' )
     
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Code (Text):
    SELECT customer_id
    FROM   transactions
    WHERE  shipmentstatus = 'Yes'
    MINUS
    SELECT customer_id
    FROM   transactions
    WHERE  shipmentstatus = 'No';
     
  6. sunny1234

    sunny1234 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Sorry, i missed to add some more information.. Table structure is changed now.

    Order table:
    Order No, Order date, ItemStatus
    1234 , 01-01-2014 Hold
    4567 , 01-31-2013, Sent
    6677, 02-02-2015, Hold


    Transaction Table:
    Order No, Item No, ShipmentStatus
    1234 45566 Yes
    1234 54345 Yes
    1234 95586 Yes

    4567 78787 Yes
    4567 55788 Yes

    6677 46777 No
    6677 54666 Yes

    In order table, each Order number can have multiple items placed which is in transaction table.
    For eg. Order no. 1234 in Transaction table all item no are shipped and in order table Item status is still Hold for 1234. It's not updated. I want to query all the records for order no which is in hold status and Shipment Status is Yes.
     
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Code (SQL):

    SELECT * FROM Orders O,Transactions T WHERE  O.order_no=T.order_no
    AND O.item_status='HOLD' AND T.shipment_status='YES';
     
     
  8. sunny1234

    sunny1234 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hi Vicky, thanks, the above query will also return Order NO. 6677. Please read my question one more time. I want to read through all the records in transaction table with ShipmentStatus = Yes for all the transaction of the given order.
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Try below query...

    SELECT * FROM Orders O
    where o_Order_no not in (select distinct T.order_no from Transactions T WHERE o_Order_no=T.order_no
    AND T.shipment_status ='No')
    AND O.item_status='HOLD';
     
  10. sunny1234

    sunny1234 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hi Jagadekara,
    I want to return the record from transactions table only if all the "ItemNo" for "ShipmentStatus" is 'Yes' for the given Order no. If only one record is 'Yes' and rest is 'No' i don't want to display that Order no. I want all the Shipment status for that Order no to be 'Yes' and ItemStatus 'Hold' from order table. Hope its clear now.
     
  11. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Both the information you are supplying and what you are asking for keeps changing. You are wasting other people's time and energy.
     
  12. sunny1234

    sunny1234 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Sorry, I apologize for this. I don't want to provide any wrong information and waste anybody's time. I understand everyones time is very valuable. Can someone help. In future i will make sure this doesn't repeat.
     
  13. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    HI,

    select * from transactions t, order o
    where 1=1
    and t.order_no=o.order_no
    and t.shipment_status='Yes'
    and o.item_status='Hold'
    and t.order_no not in (select t2.order_no from transactions t2 where t2.shipment_status='No')

    Hope this will work...
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):

    SQL> SELECT *
      2  FROM orders
      3  WHERE order_no IN (SELECT order_no FROM TRANSACTION WHERE shipmentstatus = 'Yes' minus SELECT order_no FROM TRANSACTION WHERE shipmentstatus = 'No')
      4  AND itemstatus = 'Hold';

      ORDER_NO ORDER_DAT ITEMSTAT
    ---------- --------- --------
          1234 01-JAN-14 Hold

    SQL>
     
     
  15. sunny1234

    sunny1234 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hi David & Jagadekara, Thanks for the query.. I tried but its not working. It's pulling the other records from transaction table for the same order. It's including the transaction record for shipment status "Yes' and 'NO' .
     
  16. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Code (SQL):


    SELECT * FROM transactions WHERE order_no NOT IN
    (SELECT order_no FROM TRANSACTION WHERE ShipmentStatus = 'NO');

     
     
  17. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula

    Hi Can you post the output came from my query?
     
  18. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I can only work with the example data you provide and there are no records with 'NO' in the text; YOU need to modify the query to address case issues in text.