Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: match 3 out of n number of records

    Hi Guys,

    can someone help me, I have a invoice that has multiple items on it and I trying to find which invoices have a certain 3 item_ids.


    here is an example of an invoice

    customer# invoice item_id amount
    000001 123 111 10.00
    000001 123 222 12.00
    000001 123 333 16.00
    000001 123 444 20.00
    000001 123 555 22.00


    Can someone please let me find all invoices with items 111,222,333 ?

    I tired item_id IN (111,222,333) but IN acts as an OR but what I need is an AND.

    thanks
    - Using Oracle 11g with Toad v12

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think you need to use 3 EXISTS clauses ANDed together.

    Andy

  3. #3
    Join Date
    Jan 2012
    Posts
    20
    Code:
    select id from invoice where item_id = 111 and id in (
    	select id from invoice where item_id = 222 and id in (
    		select id from invoice where item_id = 333
    	)
    );

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by nadecian View Post
    Hi Guys,

    can someone help me, I have a invoice that has multiple items on it and I trying to find which invoices have a certain 3 item_ids.


    here is an example of an invoice

    customer# invoice item_id amount
    000001 123 111 10.00
    000001 123 222 12.00
    000001 123 333 16.00
    000001 123 444 20.00
    000001 123 555 22.00


    Can someone please let me find all invoices with items 111,222,333 ?

    I tired item_id IN (111,222,333) but IN acts as an OR but what I need is an AND.

    thanks
    Code:
    with invoices(customer#, invoice, item_id, amount) as ( 
        values ('000001',123,111,10)
                 ,('000001',123,222,12)
                 ,('000001',123,333,16)
                 ,('000001',123,444,20)
                 ,('000001',123,555,22)
    ) 
    select invoice 
    from invoices i 
    join lateral ( values 111,222,333 ) y(item_id) 
        on i.item_id = y.item_id 
    group by invoice 
    having count(distinct i.item_id) = 3
    You probably won't need the lateral clause, aka join ( values 111,222,333 ) y(item_id) will work just fine
    --
    Lennart

  5. #5
    Join Date
    Aug 2011
    Posts
    34
    thanks for all the help guys, I started out using 3 INTERSECT queries but then switched to the lateral join cause its tighter code.

    Thanks again!
    - Using Oracle 11g with Toad v12

Posting Permissions

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