Need help composing a difficult query

[DaveButler]

Are you a competent SQL developer? If so, please help me compose this seemingly simple, yet tricky, query.

Assume the data structure in question is the typical Order and LineItem. For each Order row, there are many LineItems associated with it. I need for the query to return *all* LineItem rows for each order that contain a purchase of product "X".

Assume the following data structures.

Table: Order
Column: OrderNum (PK)

Table: LineItem
Column: OrderNum (PK, FK)
Column: ProductID (PK)

So, let's assume that the database contains 3 orders.

Order 1 is for products A, B, and X (1 Order, 3 LineItems).
Order 2 is for products C, D, and E (ditto).
Order 3 is for products X, Y, and Z (ditto).

The data in LineItem looks like this.

1,A
1,B
1,X
2,C
2,D
2,E
3,X
3,Y
3,Z

The query needs to return the following rows of LineItem.

1,A
1,B
1,X
3,X
3,Y
3,Z

What does the query look like? I'm at a loss about how to get started.

Thanks for your help !!!

Regards,
Kim

 

[fluteplr]

select * from lineitem
where exists
(select ordernum from lineitem li2
where li2.ordernum = lineitem.ordernum and
li2.productnum = 'X')

 

[angiole]

You may also try the following, which avoids creating a join:

Select   *
From     LineItem
Where    OrderNum in (
         Select OrderNum 
         From   LineItem
         Where  ProductID = 'X')

Cheers AA 8~)

 

[r937]

angiole, flutepr's query didn't have a join either ;-)

using a subquery (whether correlated or not) merely avoids creating a join in the actual sql statement submitted to the database engine

once the database engine parses and optimizes the sql, however, it almost always executes it as a join -- of course, this depends on which database engine we're talking about, which is probably off topic (this is, after all, the ansi sql forum)

nevertheless, there are databases *cough*mysql*cough* which would prefer you write your sql statements with joins rather than subqueries

rudy

http://rudy.ca/

 

[angiole]

Rudy Rudy, feeling frustrated?

where li2.ordernum = lineitem.ordernum

Looks like a join from here.

Optimizer parsing aside, just presenting an alternative.

CuriousAboutSQL needed to build a query, and he has some options now.

Cheers AA 8~)

 

[fluteplr]

So, did any of the suggestions work for you?

 

[DaveButler]

Thanks to everyone for their input! Angiole's solution was the following.

Select *
From LineItem
Where OrderNum in (
Select OrderNum
From LineItem
Where ProductID = 'X')

How would it differ if you needed to compare two columns in the main query with two corresponding columns in the subquery? It seems like you'd need to concatenate the two column values.

BTW, I'm using Access SQL, if that matters.

Thanks again,
Kim

 

[fluteplr]

If you use the exist format I listed above you just add the second criteria to the where clause of the subselect.

I don't know for sure if that will work in access which is not really ansi compliant. You might want to continue this in an access forum.

 

[angiole]

Good Morning All,

Regrettably, I can't get Access to run on my p/c today, but the following does work in ORacle. Perhaps someone can test?

SELECT  *
FROM    TABLE1
WHERE   (COL1,COL2) IN 
        (SELECT COL3,COL4
         FROM   TABLE2
         WHERE  ...)

I haven't tested whether this method is faster than the concatenation method, but it seems it might be.

Cheers

AA 8~)

 

[r937]

nope, at least not in access 97, angiole, and i kind of doubt in later versions, that rowset thing is definitely advanced sql

good one

rudy

 

[DaveButler]

Access doesn't like that Oracle syntax. Through trial and error, I have determined that Access prefers this format.

SELECT *
FROM TABLE1
WHERE COL1 & COL2 IN
(SELECT COL3 & COL4 as blah
FROM TABLE2
WHERE ...)

Gotta love it.

Thanks to all for your input.