$row value lost when using LEFT JOIN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

$row value lost when using LEFT JOIN

Post by deejay »

Hi

I'm doing a database search using LEFT JOIN but lose the values that are present in both tables. heres the code I'm using to test this

Code: Select all

 
$selectquery = "SELECT * FROM listings AS li  LEFT JOIN weeks AS w ON w.llid = li.llid  ORDER BY RAND() LIMIT 1";
//echo "$selectquery";
$result = mysql_query($selectquery)
or die ("Query failed");
while ($row = mysql_fetch_array($result))
{
$variable1=$row["rid"];
echo $variable1;
echo '<br>';
$llid=$row["llid"];
echo $llid;
$picthree=$row["img3"];
echo $picthree;
echo '<br>';
$picfour=$row["img4"];
echo $picfour;
echo '<br>';
$addtwo=$row["addtwo"];
echo $addtwo;
echo '<br>';
}
 
 
the only row values I don't get back are llid & rid - which appear in both table listings and weeks.


Thanks in advance for any help.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: $row value lost when using LEFT JOIN

Post by onion2k »

Try being more specific about what data your query should return. Instead of '*' list the fields with their table names eg "li.llid, w.rid".
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Re: $row value lost when using LEFT JOIN

Post by deejay »

i tried

Code: Select all

$selectquery = "SELECT li.llid, w.rid, li.img3, li.img4, li.addtwo FROM listings AS li  LEFT JOIN weeks AS w ON w.llid = li.llid  ORDER BY RAND() LIMIT 1";
is that what you meant,
but it gave me the same result. I think i need to use another way to JOIN other than left as I understand it returns columns on right as NULL.
maybe search both tables and use a WHERE statement, the only thing I can't really get my head around is that sometimes the weeks table will be completly empty. will have to give it some thought.


Thanks anyway.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: $row value lost when using LEFT JOIN

Post by califdon »

deejay wrote:i tried

Code: Select all

$selectquery = "SELECT li.llid, w.rid, li.img3, li.img4, li.addtwo FROM listings AS li  LEFT JOIN weeks AS w ON w.llid = li.llid  ORDER BY RAND() LIMIT 1";
is that what you meant,
but it gave me the same result. I think i need to use another way to JOIN other than left as I understand it returns columns on right as NULL.
maybe search both tables and use a WHERE statement, the only thing I can't really get my head around is that sometimes the weeks table will be completly empty. will have to give it some thought.

Thanks anyway.
No, a LEFT JOIN means "return ALL rows from the first table I name in the "FROM" clause, and ONLY THOSE ROWS from the second table if they match the "ON" criteria. I don't immediately see why you have missing data. Are you really sure that the "missing" data are not just data from 'weeks' in cases where the id's don't match?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: $row value lost when using LEFT JOIN

Post by califdon »

deejay wrote:i tried

Code: Select all

$selectquery = "SELECT li.llid, w.rid, li.img3, li.img4, li.addtwo FROM listings AS li  LEFT JOIN weeks AS w ON w.llid = li.llid  ORDER BY RAND() LIMIT 1";
is that what you meant,
but it gave me the same result. I think i need to use another way to JOIN other than left as I understand it returns columns on right as NULL.
maybe search both tables and use a WHERE statement, the only thing I can't really get my head around is that sometimes the weeks table will be completly empty. will have to give it some thought.

Thanks anyway.
No, a LEFT JOIN means "return ALL rows from the first table I name in the "FROM" clause, and ONLY THOSE ROWS from the second table if they match the "ON" criteria.

Oh, wait a minute--did you mean to say "w.llid = li.llid"? Are the fieldnames the same in both tables? Also, is it correct that you only want to return one field (rid) from weeks? And a random row if there are more than one match? You probably have a reason to do that, but it struck me as slightly unusual, so I thought I'd ask, to make sure that's what you meant.
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Re: $row value lost when using LEFT JOIN

Post by deejay »

califdon wrote: No, a LEFT JOIN means "return ALL rows from the first table I name in the "FROM" clause, and ONLY THOSE ROWS from the second table if they match the "ON" criteria.
thanks for your reply.

If this is the case all my problems stem from my understanding of how LEFT JOIN works, I thought that when using ON that would provide the link point.
What I wanted is random search for a property (listings table) and then to search in 'weeks' to see if there are any dates available.

i managed to write this hack

Code: Select all

 
$selectquery = "SELECT * FROM listings ORDER BY RAND() LIMIT 1";
 
$result = mysql_query($selectquery)
or die ("Query failed");
while ($row = mysql_fetch_array($result))
{
$variable1=$row["rid"];
 
$datequery = "SELECT * FROM weeks WHERE rid=$variable1 LIMIT 1";
$dateRst = mysql_query($datequery);
 
// some times this table will be empty - so have included the following if statement rather that die
    if ($dateRst) {
    $dateRow = mysql_fetch_array($dateRst);
    }
 
 
 


where I have put the weeks search inside the row while.



Thanks
Post Reply