[ MySQL selection using LEFT JOIN ]

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

Answer 1

---

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.

Answer 2

---

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