Jump to content

left join in a where clause


dewdo

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.
Link to comment
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?




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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.