[Resolved] Need help with this join query???

niroshan

Hi all,

I have a serious problem and i dont no how to solve this. I have tryied my best and still cann`t figure it out 😕

I have 4 tables called,
1.mos_member_countries
- id double (prime)
- name varchar(255)

2.mos_member_counties
- id double (prime)
- name varchar(255)
- countryid double

mos_member_counties table is link to mos_member_countries table using the countryid .

3.mos_members
- memberid double (prime)
- company varchar(255)
- countyid double

mos_members is link to mos_member_counties table using the countyid.

(*) there is a table called mos_keys and it has memberid and keyid, and it`s not need here so i didnt post the db structure of that table. mos_members has many mos_keys so they have many to many relationship so i have below table to link them up.

4.mos_member_and_keys
- id double (prime)
- keyid double
- memberid double

so thats about it for my table structures.

in my search query i have checkboxes for mos_keys so user can include more than 1 key in there search. Also he can include country, county and companyname in there search.

So my problem arrise when i try to include more than 1 key in my search.
Ex: member1 has key 1 and 2 and member2 has key 1 and 3,
now i want all the members which has key 1 and 2.

I tried many queries but i get both of them as my result because both of them has key 1. But i just want only the member which has key 1 and 2.

does anyone no how to solve this problem.

All the posts are welcome,

Thanx in advance,

Regards,
Niroshan

leatherback

Hi,

Assuming you have the checkboxes defined as an array:

you can go through the array, and define the subsetquery;

in the processing page:


$country = $_POST[country];

if(is_array($country))
  {
   foreach($country as $key => $value)
      {
       $country_subset[$i] = "countryid = \'$value\'";
       $i++;
        }
$query = implode( $country_subset, " and ");
  }
else
  {
  if($country <> "")
     {
      $query = "countryid = \'$country\'"; 
      }
  }

echo $query;

this is just an example for the country. There likely some typos and stuff. But the idea should get you started.

niroshan

Hi,
thanx you very much for the reply but i`m affraid still didnt able to solve the problem.

Let me simplyfy my problem so you guys can undestand it better.
lets take only one table.

mos_member_and_keys
- id double (prime)
- keyid double
- memberid double

here are some sample data so u`ll get the idea of the table.

id keyid memberid

1 1 1

2 4 1

3 5 1

4 1 2

5 1 3

6 4 3

now i want to get the member ids which has key as 1 and 4. so the result should have only the memberid [B]1 and 3[/B] not 2 because he dowsnt have keyid 4.

I tryed my best but still cannt figure it out. I used IN() function but that return me all the memberid`s because that function wont validate the both condition instead if one condition is true it return the memberid.

sql="SELECT memberid FROM mos_member_and_keys WHERE keyid IN(1,4);

Desperatly need your help on this guys..

Thanx in advance,

Regards,
Niroshan

mrhappiness

SELECT COUNT(something) the_count

WHERE a_column IN (1, 4)

HAVING the_count = 2

combine those three lines and you should get what you want

niroshan

Hi,

Thank you very much mrhappiness and also leatherback for all u r support. I did the query according to the query mrhappiness have sent me and it really worked. 😃

thanx a lot agin for all u r support .. Keep up the good work

for who wondering about the query here it is so anyone has a same problem i guess this would help you.

$sql="SELECT COUNT(mos_member_and_keys.keyid) AS keycount,mos_members.memberid,mos_members.title,mos_members.firstname,mos_members.lastname,mos_members.company,mos_members.address,mos_members.telephone,mos_members.fax,mos_members.email,mos_members.website,mos_members.mobile,mos_member_and_keys.memberid FROM mos_members,mos_member_and_keys WHERE mos_member_and_keys.keyid IN (8,5,2,10) AND mos_members.memberid=mos_member_and_keys.memberid GROUP BY mos_member_and_keys.memberid HAVING keycount=4";

Regards,
Niroshan