Hi

I have a master/detail relationship between orders/items tables.  I want to return all the orders which include certain products.  This works fine if I
want say orders which have item A1 or item A2 as below.

Select OrderNo,OrderDate from Orders o
Left Outer Join OrdItems I On (o.OrderNo=c.OrderNo)
Where (i.ItemNo=’A1’) Or (i.ItemNo=’A2’)

But I cant figure out the sql required to return orders which have both A1 AND A2.

Select OrderNo,OrderDate from Orders o
Left Outer Join OrdItems I On (o.OrderNo=c.OrderNo)
Where (i.ItemNo=’A1’) AND (i.ItemNo=’A2’)

ItemNo cannot be A1 and A2 so this does not work.  Any ideas?

Thanks in advance!

"Paul Harrison" <pauljharrison@btinternet.com> wrote in message
news:EA266662-B58D-4B89-B1F0-355B78B8989F@news.elevatesoft.com...
> Hi
>
> I have a master/detail relationship between orders/items tables.  I want
> to return all the orders which include certain products.  This works fine
> if I
> want say orders which have item A1 or item A2 as below.
>
> Select OrderNo,OrderDate from Orders o
> Left Outer Join OrdItems I On (o.OrderNo=c.OrderNo)
> Where (i.ItemNo='A1') Or (i.ItemNo='A2')
>
> But I cant figure out the sql required to return orders which have both A1
> AND A2.
>
> Select OrderNo,OrderDate from Orders o
> Left Outer Join OrdItems I On (o.OrderNo=c.OrderNo)
> Where (i.ItemNo='A1') AND (i.ItemNo='A2')
>
> ItemNo cannot be A1 and A2 so this does not work.  Any ideas?
>

You need a script. You need first a select that will give you an
intermediate table with order numbers that have both ones and twos,
basically scanning the whole table.

Select SUM(IF(ItemNo = 'A1' then 1 else 0)) Ones, SUM(IF(ItemNo = 'A2' then
1 else 0)) Twos, OrderNo MOrderNo
into memory\temp
from OrdItems
group by OrderNo
having (Ones > 0) and (Twos > 0);

then you join this table to your select for the orders.

Question for the members of this group: why do folks insist on using left
joins when they are not required? For example in Paul's original query,
what's the use of having a left join if you are selecting based on the
contents of the right table? I ask because I've seen a number of SQL queries
using that syntax. Maybe it is a DBISAM thing

Robert

"Paul Harrison" <pauljharrison@btinternet.com> wrote in message
news:EA266662-B58D-4B89-B1F0-355B78B8989F@news.elevatesoft.com...
> Hi
>
> I have a master/detail relationship between orders/items tables.  I want
> to return all the orders which include certain products.  This works fine
> if I
> want say orders which have item A1 or item A2 as below.
>
> Select OrderNo,OrderDate from Orders o
> Left Outer Join OrdItems I On (o.OrderNo=c.OrderNo)
> Where (i.ItemNo=’A1’) Or (i.ItemNo=’A2’)
>
> But I cant figure out the sql required to return orders which have both A1
> AND A2.
>
> Select OrderNo,OrderDate from Orders o
> Left Outer Join OrdItems I On (o.OrderNo=c.OrderNo)
> Where (i.ItemNo=’A1’) AND (i.ItemNo=’A2’)
>
> ItemNo cannot be A1 and A2 so this does not work.  Any ideas?
>
> Thanks in advance!
>
Try using the IN clause like IN ('A1','A2')

Frans


IN basically OR's the test and Paul wants AND


Roy Lambert

Robert



>Question for the members of this group: why do folks insist on using left
>joins when they are not required? For example in Paul's original query,
>what's the use of having a left join if you are selecting based on the
>contents of the right table? I ask because I've seen a number of SQL queries
>using that syntax. Maybe it is a DBISAM thing


Don't know about anyone else but in my case its simple incompetence. Having picked up the bit of SQL I do know by practice using DBISAM I just don't know any better and I suspect the same is true for a lot of the users.

Its also the reason why I (unlike Ole) don't care about compliance to the sql2003 standard. I just want something simple and fast <vbg>

Roy Lambert

Ouch!
I don't know why, but I always mentally build the queries in terms of left
outer joins first, and only after that I think about the possibility of
using another join type...

> using that syntax. Maybe it is a DBISAM thing
I don't think so. I think it's because the rigth hemisphere of my brain is
degrading faster then the left hemisphere Age effects...

--
Fernando Dias

"Fernando Dias" <fernandodias.removthis@easygate.com.pt> wrote in message
news:BA67CB29-E4F9-43CD-87FD-B2010E86D5DA@news.elevatesoft.com...
> Ouch!
> I don't know why, but I always mentally build the queries in terms of left
> outer joins first, and only after that I think about the possibility of
> using another join type...
>

The problem IMO is that even if it causes no problem, as in Paul's original
query, it is misleading. If you go back to such a query after some time, or
if somebody else has to read it, the first thing that comes to mind when you
see a left outer join is "author wants items from the left table even if
there are no matches on the right table". Not true.

Also, depending on the query, you'll get the wrong results. For example, in
the script I wrote for Paul

1. Select all ids that have both lines A1 and A2
2. Join that selection to orders table to get the orders that have both.

If you code the second select with a left outer join, you end up selecting
all orders

>> using that syntax. Maybe it is a DBISAM thing
> I don't think so. I think it's because the rigth hemisphere of my brain is
> degrading faster then the left hemisphere

Ah, that.

Robert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:E1595E72-59B5-403A-A265-1F3D4A44B4AE@news.elevatesoft.com...
> Frans
>
>
> IN basically OR's the test and Paul wants AND
>
>
> Roy Lambert
>

<blush>

Robert thanks for your help this works great!