MySQL selection using LEFT JOIN

Question

I am trying to join two tables and select from 'railstp'. I have simplified the tables.

Table 'railstp' looks like below

count  startdate   startlocation    atoc

  1    2013-09-28     lester          a
  2    2013-09-28     nottm           a
  3    2013-09-20     lester          a   
  4    2013-09-28     birm            a

Table 'location' looks like below

count  startlocation  goodlocation

  1      lester         Leicester
  2      nottm          Nottingham

I am trying to get the 'proper' (goodlocation) description from the table 'location' to replace the abbreviated description (startlocation) and also SELECT all the locations with a startdate of 2013-09-28. If there is not a 'proper' description it would be great if I just show the abbreviated description

So the result I am looking to achieve is

2013-09-28    Leicester    a
2013-09-28    Nottingham   a
2013-09-28    birm         a

My code is as below:-

require('connect_db.php');
mysqli_select_db($mysql_link,"Timetable");
function show_records($mysql_link)
{
$q="SELECT railstp.startdate,railstp.startlocation,railstp.atoc,location.startlocation,location.goodlocation
FROM railstp
LEFT JOIN location
ON railstp.startlocation=location.startlocation
WHERE railstp.startdate='2013-09-28'
ORDER BY startdate";

$r=mysqli_query($mysql_link,$q);

if ($r)
{
echo "<Table id='customers'>
<tr>
<th>From</th>
<th>Departing</th>
<th>ATOC</th>
</tr>";

while ($row=mysqli_fetch_array($r,MYSQLI_ASSOC))
{
echo "<tr>";
echo "<td>".$row['startdate']."</td>";
echo "<td>".$row['startlocation']."</td>";
echo "<td>".$row['atoc']."</td>";
echo "</tr>";
}
echo "</Table>";
}
else {echo '<p>'.mysqli_error($mysql_link).'</p>' ;}
}
show_records($mysql_link);
mysqli_close($mysql_link);

The selection does not show the 'Proper' (goodlocation) description - ie it should show Nottingham instead of nottm and Leicester instead of lester

Thank you for your help

Marty McVry · Accepted Answer · 2013-09-15 18:47:16Z

3

You can use the SQL-function COALESCE() for that.

This function uses the first non-null value in its arguments.

SELECT railstp.startdate,
       COALESCE(location.goodlocation, railstp.startlocation) as startloc,
       railstp.atoc
FROM railstp
LEFT JOIN location
ON railstp.startlocation=location.startlocation
WHERE railstp.startdate='2013-09-28'
ORDER BY startdate

So here, if location.goodlocation is NULL, railstp.startlocation is used.

See SQL-fiddle.

UPDATE

If you want to add an endlocation, you can join the location table again. I'll point out that, in order to make things a bit easier, I've added table aliases to distinguish between the different tables.

SELECT r.startdate,
       COALESCE(l1.goodlocation, r.startlocation) as startloc,
       COALESCE(l2.goodlocation, r.endlocation) as endloc,
       r.atoc
FROM railstp r
LEFT JOIN location l1
ON r.startlocation = l1.startlocation
LEFT JOIN location l2
ON r.endlocation = l2.startlocation
WHERE r.startdate='2013-09-28'
ORDER BY r.startdate

This, off course, if the location table is used for both start and end locations.

edited Sep 15, 2013 at 18:47

answered Sep 15, 2013 at 16:18

Marty McVry

3,0711 gold badge22 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2635961 Over a year ago

If I want to add a new column called endlocation in railstp but look up the same 'proper' description from the location table how would I do that? Many thanks.

Gordon Linoff · Accepted Answer · 2013-09-15 16:28:13Z

1

You have two columns with the same name in your query. There is no way to tell which one you want in the php code, so the first is chosen. Use as to rename one of them:

SELECT rs.startdate, rs.startlocation as shortstartlocation, rs.atoc,
       coalesce(l.startlocation,  rs.startlocation) as startlocation,
       l.goodlocation
FROM railstp rs LEFT JOIN
     location l
      ON rs.startlocation = l.startlocation
WHERE rs.startdate = '2013-09-28'
ORDER BY startdate

edited Sep 15, 2013 at 16:28

answered Sep 15, 2013 at 16:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

user2635961 Over a year ago

Thanks Gordon - should I need to echo shortstartlocation as I am struggling to make this one work?

Collectives™ on Stack Overflow

MySQL selection using LEFT JOIN

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related