Beginners Question

Help . . . . A few days back I decided to teach myself SQL and set up an Access 97 database and ASP pages to try it all out. All going well, Inserted, selected, updated and deleted records without a problem.

However, I have now been struggling for 4 hours with what must be a basic problem . . .

I am trying to extract records from 2 tables with an AND relationship to the same field in multiple records in the second table. For example:

Table 1 - Contact
id
name

Table 2 - Fruit
contactid
Fruit

So a contact can have lots of different fruit he likes. If I want to extract all contacts who like Apples - no problem.

However how do I extract all contacts who like Apples AND Oranges. If I add a "WHERE Fruit = 'Apples' and Fruit = 'Oranges'" I get no records returned as I assume it is looking for both fields in the same record.

I realise this is basic level stuff, but I have struggled and cannot solve it!

Cheers

Nigel

Try "WHERE Fruit = 'Apples' OR Fruit = 'Oranges'"

Hi Doug

Thanks for the reply, but this does not seem to give the desired effect.

I am trying to extract the contacts who like both Apples and Oranges, the OR gives me contacts who like either Apples or Oranges.

Does this make sense?

Cheers

Nigel

Then Try

Hi Memnoch

The and is where I started but it appears to be checking a single record not each record in the Fruit table - so never returns anything!

I am sure this must straightforward and I am just missing the point somewhere!

Thanks

Nigel

try this

Hi again

Closer . . . first time I ran it listed all items in Table1 twice against both apples and oranges. I then added to WHERE "AND (A.id = B.contactid)" to link the 2 tables. This then gave me the contacts with Apples or Oranges again. IE

Alan Apples
Bill Apples
Bill Oranges
Dave Oranges
etc

Where all I really wanted was Bill! I guess I can sort this programmatically from here. I was only doing it as a learning exercise anyway! I think I have a bit of reading to do to work out what your SQL statements was doing!

Thanks for you help

Cheers

Nigel

If Bill has both apples and oranges, then the query would return at least 2 rows.

Thanks

As you say, I can programmatically check the Recordset for the number of matches required.

Out of interest, is this a restriction in SQL or bad design on behalf of my DB? I would of thought this sort of query would of been common place (IE what customer ordered a chair and a desk, what hotel accepts credit card and debit card, etc).

Sorry for all the questions, just trying to get an understanding of what can and cannot be done easily.

Cheers

Nigel

Generally, the query would be something like this.

Let's assume that Bob had Apples and Oranges...The names are filled into a drop down list. When the user selects a user in the drop down list, the query below gets passed to another page.


So the page could display

Hi

Yes I can see that, but what I was trying to do was a search the other way round . . . . . IE
What contacts like apples and oranges - return Bob. (a bit like searching a CV for expertise - I want someone with SQL and VB).

Cheers

Hi

This still brings up everyone who likes apples or oranges. If I change the and to an or it then brings up nothing!! Arghhh!

Don't worry any further, as I say it was only a learning exercise anyway - I will return to the problem when I have more knowledge.

Thanks for your time, its appreciated.

Cheers

Nigel