Re: Joining Tables for Search

Hello Harry, you can use left join: http://dev.mysql.com/doc/mysql/en/JOIN.html $query = " SELECT members.id, firstname, familyname, middlename, fathername, region3.region3, region4, members.country, country.countryname, MATCH(firstname, middlename, familyname, fathername) AGAINST ('$keywords' IN BOOLEAN MODE) AS score FROM members left join country on members.country = country.id left join region3 on members.region3 = region3.id WHERE MATCH(firstname, middlename, familyname, fathername) AGAINST ('$keywords' IN BOOLEAN MODE) ORDER BY score DESC"; good luck. HG> Hi, HG> I want to run a query on a table while joining it with other tables. HG> Table 1- members HG> id - firstname - middlename - ....- country - region3 HG> Fields country and region3 hold the integer codes for country and region3 is state HG> Table 2- country HG> id - countryname HG> Countryname is where the actual Country name is stored and id is the country code HG> Similarly Table 3 - region3 HG> id - region3 where region3 is the state name and id is the region code. HG> I perform the following query to search the names in the database but the result only gives HG> me the integer values for Country and Region3. That's because, the code is only stored in the HG> database. HG> But I need to join the table 2&3 with 1 so I can extract names of country or region3 HG> rather than integer values. HG> How would I modify the query below? HG> $keywords are the search words. HG> $query = " HG> SELECT id, firstname, familyname, middlename, fathername, region3, region4, country, HG> MATCH(firstname, middlename, familyname, fathername) AGAINST ('$keywords' HG> IN BOOLEAN MODE) AS score HG> FROM members HG> WHERE MATCH(firstname, middlename, familyname, fathername) AGAINST HG> ('$keywords' IN BOOLEAN MODE) ORDER BY score DESC"; HG> Thanks HG> Harmeet -- Best regards, Pablo