Jump to content


Photo

left join in a where clause


  • Please log in to reply
5 replies to this topic

#1 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 19 September 2006 - 04:33 PM

$sql = "SELECT DISTINCT tb1.label, tb2.label2 FROM tb1, tb2, tb3 WHERE '(SELECT tb2.label2 FROM tb2 LEFT JOIN tb3 ON tb2.label2 = tb3.label2)'";

why wont this work?

I am trying to display the entire contents of two tables and then show which of the rows match are recorded together in a third table.

#2 jvrothjr

jvrothjr
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts
  • LocationOhio

Posted 20 September 2006 - 02:06 PM

here is a join that will get you matching records then you will need to insert this to the third table
"SELECT DISTINCT tb1.label, tb2.label2 FROM tb1, tb2, tb3 from tb2 left join tb3 on tb2.label2 = tb3.label2"

If you understand everything you know nothing!

http://rcchjr.awardspace.com/

#3 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 25 September 2006 - 06:57 PM

I have three tables

AllSTypes                          AllCPS                 
|types|                            |CPS|                 
type 1                              CP 1
type 2                              CP 2
type 3                              CP 3
type 4                              CP 4



AllCombo
|CPS|      |types|      |APSID|
CP 1        type 2        1
CP 2        type 1        2
CP 2        type 2        3
CP 4        type 3        4


I need results that show a full list of Types and CPS and then says whether there is an entry for that combo in AllCombo or not...

ideas?






#4 jvrothjr

jvrothjr
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts
  • LocationOhio

Posted 25 September 2006 - 07:29 PM

$qeury = "SELECT ALLcps.CPS, allstypes.types, AllCombo.APSID
FROM allstypes INNER JOIN (ALLcps INNER JOIN AllCombo ON ALLcps.CPS = AllCombo.CPS) ON allstypes.types = AllCombo.types
WHERE (((ALLcps.CPS)='cp1') AND ((allstypes.types)='type2'));"

If you understand everything you know nothing!

http://rcchjr.awardspace.com/

#5 dewdo

dewdo
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 25 September 2006 - 10:10 PM

Thanks for the reply.
the result set I am looking for is not as specific as that statement seems to require.

I need a result set that looks like this:

                  Type 1 | Type 2 | Type 3 | Type 4
          CP 1      NO        YES      NO      NO
          CP 2      YES        YES      NO      NO
          CP 3      NO        NO        NO      NO
          CP 4      NO        NO        YES      NO

Ideas?

#6 jvrothjr

jvrothjr
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts
  • LocationOhio

Posted 27 September 2006 - 01:19 PM

I have not tested this code but I believe this is what your looking for

echo "<table><tr><td></td>";
$queryTHD = "SELECT * FROM allstypes ORDER BY types ASC";
if ($MatchTHDRow = mysql_fetch_array($SearchTHD)) {
	do{
	 	$tp = $MatchTHDRow[types];
		echo "<td>".$tp."</td>";	 
	}
	while($MatchTHDRow = mysql_fetch_array($SearchTHD));
}
echo "</tr>";
$queryCPS = "SELECT * FROM ALLcps ORDER BY CPS ASC";
$SearchCPS = mysql_query ($queryCPS);
if ($MatchCPSRow = mysql_fetch_array($SearchCPS)) {
	do{
	 	$cp = $MatchTYPRow[CPS];
		$queryTYP = "SELECT * FROM allstypes ORDER BY types ASC";
		$SearchTPY = mysql_query ($queryTPY);
		echo "<tr><td>".$cp."</td>";
		if ($MatchTYPRow = mysql_fetch_array($SearchTYP)) {
			do{
			 	$tp = $MatchTYPRow[types];
				$querycnt = "SELECT count(*) FROM AllCombo WHERE types ='tp' and CPS = 'cp'";			
			    $cntchk=mysql_result($querycnt,0,"count(*)");							
			    echo "<td>";
			    if ($cntchk == 0) {echo "NO";} else {echo "YES";}
			    echo "</td>";
			}
			while($MatchCPSRow = mysql_fetch_array($SearchCPS));
		}
		echo "</tr>";
	}
	while($MatchCPSRow = mysql_fetch_array($SearchCPS));
echo "</table>";

If you understand everything you know nothing!

http://rcchjr.awardspace.com/




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users