Select query thats realy got me stumped

[marky2coats]

Hi ppl,

Ive been trying to work how to get this done, and im starting to think my table structure is not correct to allow the query im trying to acheive.

Anyway heres an outline of the tables and the result i want

Table1: contact
Id firstname lastname
1 mark little
2 burt bertie

Table2: contact_data
contact_id data_field_id data
1 1 Vodka
1 2 Apples

Table3: contact_data_field
data_field_id field
1 Favourite Drink
2 Favourite Fruit

I want to return all contacts whos Favourite drink is vodka AND whos favourite fruit is Apples.

I can get the result for OR by using

select contact.id,contact.firstname,contact.lastname from contact
left join contact_data on contact.id = contact_data.contact_id where
(contact_data.data_field_id="1" and contact_data.data like "vodka") or
(contact_data.data_field_id="2" and contact_data.data like "apples")

But I dont get any results when I use AND when I should get the contact with ID 1.

Any help will be appreciated.

Mark.

[Lars_Berg]

select firstname,lastname
from contact inner join contact_data on contact.id = contact_data.contact_id
 inner join contact_data_field on contact_data.data_field_id = contact_data_field.data_field_id
where contact_data in ('Vodka','Apples')
group by firstname,lastname
having count(*) = 2

[DonChris]

i'm stupid - Lars got the answer...

// but change it to:
select firstname,lastname from contact inner join contact_data on contact.id = contact_data.contact_id inner join contact_data_field on contact_data.data_field_id = contact_data_field.data_field_id where contact_data.data in ('Vodka','Apples') group by firstname,lastname having count(*) = 2;

[marky2coats]

Thanks guys!,

I did forget to mention that I wanted to use wildcard % For instance. I want to get all the contacts whos Favoruite Drink is vod% AND whos Favourite Fruit is app%.

The way you have done it Lars (many thanks for that!) doesnt allow me to do this does it?

Also, by my understanding, it doesnt take into consideration what answer applies to what question. For instance if I had a contact whos favourite fruit was vodka and their favourite drink was apple it would return them even though its incorrect.

Thanks for the help.

[marky2coats]

Ok I think ive solved it. Not sure if this the most elegant of ways but it will do the trick.

CREATE TEMPORARY TABLE temp_contact (
id tinyint(4),
firstname varchar(20) default NULL,
lastname varchar(20) default NULL
) TYPE=MyISAM;

insert into temp_contact (id,firstname,lastname)
select id,firstname,lastname from contact left join contact_data on contact.id = contact_data.contact_id
where db_field_id=1 and (data like "apples" or data like "pears");

insert into temp_contact (id,firstname,lastname)
select id,firstname,lastname from contact left join contact_data on contact.id = contact_data.contact_id
where db_field_id=2 and (data like "milk" or data like "coke");

select from temp_contact group by id having count() = 2;

Anybody got any better ideas?

Mark.