Jump to content

Rewriting the query - a problem with joins


swatisonee

Recommended Posts

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.
Link to comment
https://forums.phpfreaks.com/topic/33862-rewriting-the-query-a-problem-with-joins/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.