Getting Caught in a Loop

[swatisonee]

hi,

a. I have 2 Tables: Table A and Table B

b. I have to select all records from Table A that donot appear in Table B . 1 field in both is common .

c. I dont have a problem selecting data that appears in both tables where the same field is in common but
the reverse is getting caught in a loop and i'm not able to figure out what the error is.

d. The error is that the else statement gets executed.

Would appreciate some guidance pleas.e . Thanks.Swati

[code]$sqlb= "SELECT * FROM `Enq` WHERE  Category`!= 'D' && `Category`!= 'A' && fid='$fid'";
$resultb=mysql_query($sqlb);
if ($myrowb=mysql_fetch_array($resultb))
{
do
{
$enqid=$myrowb["Enqid"];

$sqlc="SELECT * FROM `order` WHERE `Enqid`= $enqid ";
if ( !@mysql_query($resultc) )
{

do {

     printf("<tr><td><input type=\"radio\" name=\"choice\" value=%d><td>
     <font size=2 face=Tahoma color=blue>%d<td>
     <font size=2 face=Tahoma color=blue>%s<td>
     <font size=2 face=Tahoma color=blue>%s<td>
</tr>",
  $myrowb["Enqid"],
   $myrowb["fid"],
  $myrowb["Company"],
  calculatedate($myrowb["Date"]) );

}
while ($myrowc = mysql_fetch_array($resultc));
}

}
while ($myrowb= mysql_fetch_array($resultb));
}

else {
    echo "Sorry, no records were found!";
}[/code]

[Barand]

To find unmatched records use LEFT JOIN and search for results where the data in the right table is NULL (ie no data)


[code]SELECT a.* FROM tableA a LEFT JOIN tableB b
ON a.common = b.common
WHERE b.common IS NULL[/code]

[sasa]

[!--quoteo(post=353947:date=Mar 11 2006, 05:37 PM:name=swatisonee)--][div class=\'quotetop\']QUOTE(swatisonee @ Mar 11 2006, 05:37 PM) [snapback]353947[/snapback][/div][div class=\'quotemain\'][!--quotec--]
hi,

a. I have 2 Tables: Table A and Table B

b. I have to select all records from Table A that donot appear in Table B . 1 field in both is common .

c. I dont have a problem selecting data that appears in both tables where the same field is in common but
the reverse is getting caught in a loop and i'm not able to figure out what the error is.

d. The error is that the else statement gets executed.

Would appreciate some guidance pleas.e . Thanks.Swati

[code]$sqlb= "SELECT * FROM `Enq` WHERE  Category`!= 'D' && `Category`!= 'A' && fid='$fid'";
$resultb=mysql_query($sqlb);
if ($myrowb=mysql_fetch_array($resultb))
{
do
{
$enqid=$myrowb["Enqid"];

$sqlc="SELECT * FROM `order` WHERE `Enqid`= $enqid ";
if ( !@mysql_query($resultc) ) // <-- look this line
{

do {

     printf("<tr><td><input type=\"radio\" name=\"choice\" value=%d><td>
     <font size=2 face=Tahoma color=blue>%d<td>
     <font size=2 face=Tahoma color=blue>%s<td>
     <font size=2 face=Tahoma color=blue>%s<td>
</tr>",
  $myrowb["Enqid"],
   $myrowb["fid"],
  $myrowb["Company"],
  calculatedate($myrowb["Date"]) );

}
while ($myrowc = mysql_fetch_array($resultc));
}

}
while ($myrowb= mysql_fetch_array($resultb));
}

else {
    echo "Sorry, no records were found!";
}[/code]
[/quote]
look lin 9 change '$resultc' to '$sqlc'

[swatisonee]

Hi,

Thanks Barand, Sasa.

I struggled to get the Join going and after lots of trial and error got it to work - it did not work till i put backticks for the table names in the on clause and also on column name in the where clause.


Thanks again.
Swati