Using Crystal Reports 2003 and accessing several database tables (via ODBC) I need to select all of the material ids associated with invoices that contain a specific material id. There are about 15,000 invoices that will contain approximately 70,000 material ids. (There is no restriction on the number of material ids that can be associated with a given invoice.) The field names are: InvoiceNumber and MaterialId

 

In effect I’m trying to say “If InvoiceNumber contains MaterialId “51-103-000000”, then select all material ids associated with that invoice number.

 

Can anyone help me properly structure the Select Statement?

Thanks,

 

Krazy (Bill) Kasper

Thanks!!!

You seem to have got confused with the Aliasing of the table...
......from InvoiceDetail st

Here you have aliased InvoiceDetail as st and hence shoul;d always refer to it as st.  In the example sent, st was an alias for Some Table, so I'll use ID for invoice detail and MD fro MaterialDetail.

Something like the following (untested).......
Basically does the join between MD and ID but will only pull back rows where the invoice contains '51-103-000000'.  Remember the bottom select is sort of seperate and thus is not refered to as ID.

Hope this helps........


SELECTID.MaterialId, ID.InvoiceId FROM InvoiceDetail ID
INNER JOIN MaterialDetail MD ON
  MD.MaterialId = ID.MaterialId
WHERE
ID.InvoiceId in
(SELECT InvoiceId From InvoiceDetal WHERE MaterialId = 51-103-000000')