swatisonee Posted January 12, 2007 Share Posted January 12, 2007 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 tableEx. 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 More sharing options...
btherl Posted January 12, 2007 Share Posted January 12, 2007 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. Link to comment https://forums.phpfreaks.com/topic/33862-rewriting-the-query-a-problem-with-joins/#findComment-158919 Share on other sites More sharing options...
Barand Posted January 12, 2007 Share Posted January 12, 2007 trySELECT a.Company FROM customer aWHERE a.cgid IN (SELECT b.cgid FROM customer b WHERE b.company LIKE '%$search%' ) Link to comment https://forums.phpfreaks.com/topic/33862-rewriting-the-query-a-problem-with-joins/#findComment-158953 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.