Rewriting the query-trouble with joins

[swatisonee]

Heres' why i have trouble with joins.

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

b. Multiple values of cid can have 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 .

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.


$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 || Customers.CGID = $cgid) ORDER BY `Company` asc ");


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 dont get a result 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 :



$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";


Any ideas ? Thanks.

[devinkray]

Does the php || statement for 'or' actually work in sql? you can try:


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


I don't understand exactly what you mean.

Also what errors are you getting?

[swatisonee]

i get a blank page. $resultc doesnt run .