SQL - "nuts" and "bolts" not either/or

[JDurbin]

Is there a single SQL statement to return the following?
List the orders that contain "nuts" AND "bolts" AND "washers".
Using someting similar to Testdata with Orditems normalized to include one item code per record for an example.
"Nuts" OR "bolts" OR "washers" is easy;
INLIST(orditems.item,"nuts","bolts","washers&quot but what about only the orders that include ALL items searched for? The order could contain other items also but at least in this example "nuts","bolts" and "washers".
This is for a string I am building which ends up macro expanded. Thanks [sig]<p>John Durbin<br><a href=mailto: john@johndurbin.com> john@johndurbin.com</a><br><a href=

http://www.johndurbin.com>

</a><br>MCP Visual FoxPro<br>
ICQ VFP ActiveList #73897253[/sig]

 

[TomasDill]

John, no easy way, you will need to make all three expressions:
MyField = &quot;nuts&quot; AND Myfield = &quot;bolts&quot; ...

As alternative, you can use ubquery, but I don;t know if it will help:
SELECT MyTable.MyField, MyTable.MyID from MyTable WHERE MyTable.MyID NOT IN
(SELECT MT.MyID From MyTable MT WHERE MT.MyField NOT IN (&quot;nuts&quot;, &quot;bolts&quot;, ...) )

Hope this helped.
[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[ChrisCarroll]

If I've understood you right, you want something like

Look at the items in an order; pick out those orders having at least one nut and bolt and washer. Hence

group the items by order; select group having ...

which becomes:

having Max( orditems.item="nuts"   ) = .T. and;
       Max( orditems.item="bolts"  ) = .T. and;
       Max( orditems.item="washers") = .T. ;
group by ordID

You have to use group by or use a subquery.
And using Max() on a boolean field is equivalent to &quot;return true if there is at least one&quot; [sig][/sig]

 

[foxdev]

This is one of those questions where I think I know the answer, but once I start answering it, I realize I have no clue.

Thanks to my associate Ed Christopher, I was able to come up with the answer. I've tested this in VFP6 and it works as John wishes.

[tt]select distinct company from orders O, orditems I ;
where i.orderno=o.orderno ;
and i.orderno in (select orders.orderno from orditems, orders ;
where part='Nuts' and orditems.orderno=orders.orderno) ;
and i.orderno in (select orders.orderno from orditems, orders ;
where part='Bolts' and orditems.orderno=orders.orderno) ;
and i.orderno in (select orders.orderno from orditems, orders ;
where part='Washers' and orditems.orderno=orders.orderno)
[/tt] [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[JDurbin]

wow different methods. If they all work it's a matter of using the fastest or easiest to construct if builidng a query string dynamically. Here's what I tested and works from Ed Rauch. I'm gonna try all methods and post times when I get back from work later today.
[tt]
SELECT * FROM Orders ;
INTO CURSOR Temp ;
WHERE OrdID IN (SELECT OrdID FROM OrdItems O1 WHERE item = 'hammer') ;
AND OrdID IN (SELECT OrdID From OrdItems O2 WHERE item = 'nails')[/tt] [sig]<p>John Durbin<br><a href=mailto: john@johndurbin.com> john@johndurbin.com</a><br><a href=

http://www.johndurbin.com>

</a><br>MCP Visual FoxPro<br>
ICQ VFP ActiveList #73897253[/sig]

 

[TomasDill]

Hi!

If this query really what you need, I propose to rearrange it so it will work by more efficient way:

SELECT Orders.* FROM Orders ;
INTO CURSOR Temp ;
WHERE EXIST (SELECT OrdItems.OrdID FROM OrdItems O1 WHERE OrdItems.OrdID = Orders.OrdID AND OrdItems.item = 'hammer') ;
AND EXIST (SELECT OrdItems.OrdID FROM OrdItems O1 WHERE OrdItems.OrdID = Orders.OrdID AND OrdItems.item = 'nails')

Above will run more quickly because 'IN' operator cause running of query once with large result set in which each step of main query searches (each search the same as LOCATE in result subset). EXIST will cause running of subquery for each record in main query, BUT, this subquery will stop running once one record returned AND it is optimizable.
[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[TomasDill]

Oops, sorry, here is correct query:

SELECT Orders.* FROM Orders ;
INTO CURSOR Temp ;
WHERE EXIST (SELECT O1.OrdID FROM OrdItems O1 WHERE O1.OrdID = Orders.OrdID AND O1.item = 'hammer') ;
AND EXIST (SELECT O1.OrdID FROM OrdItems O1 WHERE O1.OrdID = Orders.OrdID AND O1.item = 'nails')
[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[JDurbin]

Vlad it was slower and returned the incorrect number of records. It returned the same # of records as Orders (or for this example anyway I'll use that tablename) [sig]<p>John Durbin<br><a href=mailto: john@johndurbin.com> john@johndurbin.com</a><br><a href=

http://www.johndurbin.com>

</a><br>MCP Visual FoxPro<br>
ICQ VFP ActiveList #73897253[/sig]

 

[JDurbin]

Chris can you comment further on MAX()? Also as you see item is not a booolean field.
Robert thanks, it's long but I'll try it. [sig]<p>John Durbin<br><a href=mailto: john@johndurbin.com> john@johndurbin.com</a><br><a href=

http://www.johndurbin.com>

</a><br>MCP Visual FoxPro<br>
ICQ VFP ActiveList #73897253[/sig]