Q: sql select where criteria for each row is not wholly contained in the row 
Author Message
 Q: sql select where criteria for each row is not wholly contained in the row

I'm having trouble finding an elegant solution to a problem using sql.

I'm trying to run a query to select a set of rows from a table, but
the criteria for each row is not wholly contained in the row.  Here are
the details:

Table: invoice_detail
---------------------
  invoice_detail_id   (PK)
  invoice_id          (FK)
  product_id          (FK)
  quantity

So, for each product that an invoice contains, there is a row in this
table.  Now, my problem is how do i select All invoices that contain
the products 002 _and_ 005.

SELECT * FROM invoice_detail WHERE product_id = 002 AND product_id = 005

obviously won't work, since product_id cannot be two values at once.
I realize that there probably needs to be two separate queries with
some sort of join but am stuck.  Any tips or pointers would be greatly
appreciated!

-john



Thu, 18 Jan 2001 03:00:00 GMT
 Q: sql select where criteria for each row is not wholly contained in the row

I'll assume that it is possible for 1 invoice to contain at least 2 details
lines, one for each product.  If you want a list of invoice id's that
contain the two products( one or the other, possibly both ), this might
work:

select invoice_id
from invoice_detail
where product_id in( '002', '005' )
group by invoice_id

Avoid selecting all detail and group the result set, so that you only get
what you need.

Quote:

>I'm having trouble finding an elegant solution to a problem using sql.

>I'm trying to run a query to select a set of rows from a table, but
>the criteria for each row is not wholly contained in the row.  Here are
>the details:

>Table: invoice_detail
>---------------------
>  invoice_detail_id   (PK)
>  invoice_id          (FK)
>  product_id          (FK)
>  quantity

>So, for each product that an invoice contains, there is a row in this
>table.  Now, my problem is how do i select All invoices that contain
>the products 002 _and_ 005.

>SELECT * FROM invoice_detail WHERE product_id = 002 AND product_id = 005

>obviously won't work, since product_id cannot be two values at once.
>I realize that there probably needs to be two separate queries with
>some sort of join but am stuck.  Any tips or pointers would be greatly
>appreciated!

>-john



Thu, 18 Jan 2001 03:00:00 GMT
 Q: sql select where criteria for each row is not wholly contained in the row
SELECT det1.*
FROM
invoice_detail det1 JOIN (SELECT det2.detail_id
                        FROM invoice_detail det2
                        WHERE det2.product_id in ('002','005')
                        GROUP BY det2.detail_id
                        HAVING COUNT(*) = 2) as det3
        ON det1.detail_id = det3.detail_id

Or:

SELECT det1.*
FROM
invoice_detail det1
WHERE 2 = (SELECT count(*)
        FROM invoice_detail det2
        WHERE det2.product_id in ('002','005')
        AND det2.detail_id = det1.detail_id)

Quote:

>I'm having trouble finding an elegant solution to a problem using sql.

>I'm trying to run a query to select a set of rows from a table, but
>the criteria for each row is not wholly contained in the row.  Here are
>the details:

>Table: invoice_detail
>---------------------
>  invoice_detail_id   (PK)
>  invoice_id          (FK)
>  product_id          (FK)
>  quantity

>So, for each product that an invoice contains, there is a row in this
>table.  Now, my problem is how do i select All invoices that contain
>the products 002 _and_ 005.

>SELECT * FROM invoice_detail WHERE product_id = 002 AND product_id = 005

>obviously won't work, since product_id cannot be two values at once.
>I realize that there probably needs to be two separate queries with
>some sort of join but am stuck.  Any tips or pointers would be greatly
>appreciated!

>-john



Fri, 19 Jan 2001 03:00:00 GMT
 Q: sql select where criteria for each row is not wholly contained in the row
Try this: (Would appreciate feedback)

1. Find all invoices with product '002'

2. Find all invoices with product '005' and also in the list from 1 above.

SELECT
    DISTINCT invoice_id
FROM
    invoice_detail
WHERE
    product_id = '005'
AND invoice_id IN (    SELECT
                           invoice_id
                       FROM
                           invoice_detail
                       WHERE
                           product_id = '002' )

MikeC

Quote:

> I'm having trouble finding an elegant solution to a problem using sql.

> I'm trying to run a query to select a set of rows from a table, but
> the criteria for each row is not wholly contained in the row.  Here are
> the details:

> Table: invoice_detail
> ---------------------
>   invoice_detail_id   (PK)
>   invoice_id          (FK)
>   product_id          (FK)
>   quantity

> So, for each product that an invoice contains, there is a row in this
> table.  Now, my problem is how do i select All invoices that contain
> the products 002 _and_ 005.

> SELECT * FROM invoice_detail WHERE product_id = 002 AND product_id = 005

> obviously won't work, since product_id cannot be two values at once.
> I realize that there probably needs to be two separate queries with
> some sort of join but am stuck.  Any tips or pointers would be greatly
> appreciated!

> -john



Fri, 19 Jan 2001 03:00:00 GMT
 Q: sql select where criteria for each row is not wholly contained in the row

Quote:

> I'll assume that it is possible for 1 invoice to contain at least 2 details
> lines, one for each product.  If you want a list of invoice id's that
> contain the two products( one or the other, possibly both ), this might
> work:

> select invoice_id
> from invoice_detail
> where product_id in( '002', '005' )
> group by invoice_id

this is would select all invoices with 002 or 005.  I only want to get a
list of invoices that have _both_ products.

e.g. given this data:

Invoice #       Product
-----------------------
1001            002
1002            002
1002            005
1003            005

i would only want the invoice number "1002" returned.

this has really got me stumped.  I know i can always parse the results
of the query you've outlined above, to exclude certain results, but this
just seems like an adminission of defeat for sql (not to mention
inelegant).

:jason



Fri, 19 Jan 2001 03:00:00 GMT
 Q: sql select where criteria for each row is not wholly contained in the row
[snip]

Quote:
> SELECT
>     DISTINCT invoice_id
> FROM
>     invoice_detail
> WHERE
>     product_id = '005'
> AND invoice_id IN (    SELECT
>                            invoice_id
>                        FROM
>                            invoice_detail
>                        WHERE
>                            product_id = '002' )

> MikeC

Hi Mike,

Yes this works.  (btw, the DISTINCT isn't required) The following also

SELECT
        id1.invoice_id
FROM
        invoice_detail id1, invoice_detail id2
WHERE
        id1.product_id = '002'
        AND
        id2.product_id = '005'
        AND
        id1.invoice_id = id2.invoice_id

I'm also trying to see which SQL statement is easier to build when you
have 3 or 4 products and which is faster.. i think the less nested
queries the better.

-john



Sat, 20 Jan 2001 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Q: sql query where criteria is not all contained in each row

2. question regarding select (multiple rows select into one result row)

3. How to select only rows mathcing unique criteria?

4. SQL select that counts the # of parent rows w/o any child rows

5. Selecting where some rows contain zero values

6. Trying to delete rows that do not meet criteria

7. Updating the R first rows of a SELECT containing an ORDER BY statement

8. Updating the R first rows of a SELECT containing an ORDER BY statement

9. CONTAINS clause not returning rows

10. CONTAINS Not returning all rows

11. Oracle ORA-22920: row containing the LOB value is not locked

12. SQL Server 7 : Select not returning same number of rows as Oracle 8i


 
Powered by phpBB® Forum Software