×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Something other than AND or OR

Something other than AND or OR

Something other than AND or OR

(OP)
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

RE: Something other than AND or OR

Quote (NSNewey)

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

RE: Something other than AND or OR

(OP)
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

RE: Something other than AND or OR

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*"));

RE: Something other than AND or OR

Or could it be simple:
SELECT *
FROM Customers
WHERE ContactName Like "Jane*" OR ContactName Like "John*"


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

RE: Something other than AND or OR

Not quite sure that is particularly effective.

RE: Something other than AND or OR

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

RE: Something other than AND or OR

>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:

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*"));


RE: Something other than AND or OR

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close