AND Search in History field 1

[Faheem786]

Hi,

Hope some one can help me on this

I have a master table called "Contacts" and a history table called "Contact_History"

In the history table the languages spoken by a contact are stored as each records

Contact 1 has 2 history records for (lang A & lang B)
Contact 2 has 2 history records for (lang B & lang A)
Contact 3 has 2 history records for (lang A & lang C)

Question;

How to Query from the Contact table which are the contacts who can speak both languages A & B.

I should get only 2 records (Contact 1 & 2 but not 3 though he can speak any one of lang A or B)

something like this??
[blue]SELECT a.* FROM CONTACTS a, CONTACT_History b WHERE a.CONTACT_id = b.CONTACT_id AND a.CONTACT_id IN (select from the history file which has both lang a & b)
[/blue]

I'm struggling with for hours now.. Your help will much appreciated

Thanks
Ahmad
Hong Kong

 

[nigelrivett]

select a.*
from contacts a
join
(select contact_id from contact_history where lang_id in ('a','b') group by contact_id having count(*) = 2) b
on a.contact_id = b.contact_id


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Faheem786]

Hi Nigel,

Thanks a lot for your help. It works for me.. but now I have another problem.

I want to add another criteria in the query to search some other fields in the contact table.

For eg: I want to search the contacts who speaks both languges A & B and also whose status=active

** The status field is in the contact table

I want something like

select a.*
from contacts a
join
(select contact_id from contact_history where lang_id in ('a','b') group by contact_id having count(*) = 2) b
on a.contact_id = b.contact_id

[COLOR=red yellow]AND a.status = 'active'[/color]

I tried my self using different options but cannot find working.

Thanks for your time
Ahmad
Hong Kong