Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Something other than AND or OR

Status
Not open for further replies.

NSNewey

Programmer
Jun 29, 2005
125
GB
Hi
Using northwind as an example, how would I build a query to return Customers with a related contact named Jane, AND and a related contact named John? ie, I only want to return customers that have both a Jane and a John contact related to them.

I tried using AND but it returns no records because a contact cannot be both Jane AND John. OR is wrong because I want to see both, not one or the other.

My query isn't using customer and contact, but this makes it easier to explain what I'm trying to do.

I'm hoping the answer isn't to build a sub query for Jane and another for John because I have around 40 different search criteria, ie Jane, John, Susan, Billy, Adam...

Thanks for any help.
Neil
 
NSNewey said:
OR is wrong because I want to see both, not one or the other.
???
It is clean OR. If you don't like it, use IN it is shorter for more names. Finally, a table with names joined in query is a third approach.

combo
 
It can't be OR because I want to return customers (parent table) having a related contacts record (child table) called Jane and John. Using OR would show customers with either Jane or John. I want to see customers with both. Thanks
 
Using your example, something like:

SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers_1.ContactName
FROM Customers INNER JOIN Customers AS Customers_1 ON Customers.CompanyName = Customers_1.CompanyName
WHERE (((Customers.ContactName) Like "Jane*") AND ((Customers_1.ContactName) Like "John*"));
 
Or could it be simple:[tt]
SELECT *
FROM Customers
WHERE ContactName Like "Jane*" OR ContactName Like "John*"[/tt]

and, since you ask for 2 names, accept only the outcome of 2 records?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Not ‘particularly effective’. Agree. Kind of lazy approach to the issue. But if OP wants to ask for variable number of Names, that may be one way to do it. [ponder]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
>Kind of lazy approach to the issue

It's not so much that but the fact that I rather think you need to do expand your query to achieve what you are suggesting, and which I suspect will actually end up being more complex than my query ...

erm, something like:

[tt]SELECT Customers.CompanyName, Count(Customers.CustomerID) AS CountID
FROM Customers
GROUP BY Customers.CompanyName
HAVING (((Count(Customers.CustomerID))=2) AND ((First(Customers.ContactName)) Like "Jane*")) OR (((First(Customers.ContactName)) Like "John*"));[/tt]

 
Thanks guys. If it looked like it was getting complex, my backup was to loop through a recordset, do the conditional bits then and add items to the listbox rather than using a query. I've been working on that and it works fine. Just wondered if there was an SQL function that easily achieved what I needed. There is but I need to build the query in code so may as well have used a recordset.
Thanks for the advice.
Neil
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top