Need basic query help.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have table X with 5 columns. One column has the invoice number and another
has a procedure number. One invoice number may be on multiple lines with
multiple procedures, and visa versa.

I am only interested in 2 of the many procedures in the column.

I want to find out the following: Find each occurance of either of these 2
procedures and make a note of the invoice number. Then go back and tell me
all of the other procedures also in that invoice number.

So at the end, I will in effect have a new table which has all 5 columns but
only those invoices that also have at least one of the 2 procedures I am
interested in.
 
Not basic at all because the tables are not normalized properly. You should
have an INVOICE table with invoice info and a PROCEDURE table with, you
guessed it, procedure info. The primary key in INVOICE would be the foriegn
key in PROCEDURE.

Still it can be done albeit somewhat complicated:

SELECT DISTINCT X.*
FROM X
WHERE [invoice number] IN (SELECT [invoice number]
FROM X
WHERE [procedure number] IN ("NUMBER", "NUMBER2"));
 
Back
Top