Jump to content

Archived

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

dewdo

left join in a where clause

Recommended Posts

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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?




Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

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.