How can i write this query:-urgent

[Haneesh]

hi all,
i hav a table called members that holds the details of members like name and address.
And i hav another table called interest that contain interst id,member_id, interst1, interst2,interest3. Lot of members into that table( morethan 10,000+). A member can select maximum three interest.

i want to check when a new member come , then need to check, similar interessted members.
and also need to take the count of each members common interest with the new comer interst. plz help me...with query...
plz tell me the solution.. with details.

plz helppppppp

[Guptha_S]

You need to create a join between two tables using member_id
Once you create this join, you can retrive list of members with similar interest based on the interest selected by new member

[Haneesh]

GUPTHA,Thanks for u'r reply.
But here i hav 2 tables:
1. Member table: mem_id,mem_name,mem_addr
2. Interest table: int_id,mem_id,int1,int2,int3
I want to check , wen a new member comes in,then need to check ,how many similar interst for each member comparing with that new member.
plz reply me

[Guptha_S]

1. You will not be able to find until the new member select atleast one of his interest.

2. You will have to write dynamic query internally that will fire when the new member selects his interest
(I dont know on what application you are working on or which language you are using)
example if you are using check box to allow user to select his interest, then the proper event is checkbox_onclick() / checkbox_onchange - use which ever is suitable. When this event occurs, you need to execute internal query and retrieve records of members having similar interest

SELECT mem_name FROM [member table] WHERE mem_id IN (SELECT DISTINCT mem_id FROM [INTEREST Table] WHERE int1 = Chechbox_value OR int2 = Checkbox_value OR int3 = Checkbox_value)

You have to assign proper value to checkbox so as to co-relate with interest table int1 int2 int3 values

You can write above query in more than one way - that is just an example, you will have to choose the one that achieves best performance at all the times

[dilipv]

hi Haneesh,

i saw your quote. and the solution provided by " Guptha_S ", is no doubt a good and appropriate solution for your query, even i was about to give the same solution for that.

If any replies or suggestion you are most welcome.

Thank you
Jitesh
Programmer
Sharepoint Consulting