Rewriting the query - a problem with joins

[swatisonee]

Heres' why i have trouble with joins.

a. I have a customers table ( structure - cid(int), cgid(int),  and company(char)).

b. Multiple values of cid can be linked to a single cgid but cgid is a value that occurs only in the above table
Ex. cid 1,2,8 will all have a cgid of say 13  or cid 3,6,9 will have a cgid of say 4

If i want to get the value of $cgid in the foll code and then list out all cids with their addresses i cannot do it with the code below.

[code]
$sqla = "SELECT * FROM `Customers` WHERE  `Company` LIKE '%$customer%' ORDER BY `Company` asc";
$resulta = mysql_query($sqla) or die (mysql_error ());
if ($myrowa = mysql_fetch_array($resulta));
$cgid = $myrowa["CGID"];

$resultc = mysql_query("SELECT * FROM Custaddress INNER JOIN Customers ON Custaddress.CID WHERE (Custaddress.CID = $cid  OR Customers.CGID = $cgid) ORDER BY Customers.Company asc ");
[/code]

By doing this i had hoped to get not only all companies that match '%$customer% but others that also have the same value of cgid . Anyway i get a blank page with the above query so its  wrong.

Example: '%$customer% = nation . This will yield nation, national, international . But if nation and subcontinent are 2 companies having a cgid of say 1 , then when i run $sql, i not only want nation, national, international to get listed but also subcontinent. And thats where the join gives me problems.

If i did not want to list subcontinent but only nation, national, international , then i would use :

[code]

$sqla = "SELECT * FROM Customers c INNER JOIN Custaddress a USING(CID) INNER JOIN Cities t USING(CITYID) WHERE c.Company LIKE '%$customer%' ORDER BY c.Company asc";
][/code]

Any ideas ? Thanks.

[btherl]

Add [code=php:0]or die(mysql_error())[/code] after your mysql_query() call, like this:

[code=php:0]mysql_query("blah blah blah") or die(mysql_error());[/code]


And then post what it says.

[Barand]

try

SELECT a.Company FROM customer a
WHERE a.cgid IN (SELECT b.cgid FROM customer b WHERE b.company LIKE '%$search%' )