left join in a where clause

[dewdo]

$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.

[jvrothjr]

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

[dewdo]

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?

[jvrothjr]

[code=php:0]
$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'));"
[/code]

[dewdo]

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?

[jvrothjr]

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

[code=php:0]
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>";
[/code]