Query problem.

[ginerjm]

Ok - I have a working query to which I wanted to add one more item from a new table.  Thought it was easy but I must be having a bad day.  I will post the original working query (now commented out) and the new query with the added item 'driver_num':

/*
	$q = "select a.race_winner, w.wins,
			substr(a.race_winner, instr(a.race_winner,' ')+1) as last_name,
			a.race_name, date_format(a.race_date, '%m/%d') as race_date
			from trk_races a
		left outer join
			(select race_winner, count(race_date) as wins from trk_races
				where race_winner > '' and Season='$selyr'
				group by race_winner) w
		on w.race_winner = a.race_winner

		where a.race_winner > '' and a.Season='$selyr'
		order by $orderby";
*/

	$q = "select a.race_winner, w.wins,
			substr(a.race_winner, instr(a.race_winner,' ')+1) as last_name,
			a.race_name,
			date_format(a.race_date, '%m/%d') as race_date,
			d.driver_num
			from trk_races a, drivers d
		left outer join
			(select race_winner, count(race_date) as wins from trk_races
				where race_winner > '' and Season='$selyr'
				group by race_winner) w
		on w.race_winner = a.race_winner

		where a.race_winner > ''
			and a.Season = '$selyr'
			and	d.driver_season = a.Season
			and	a.race_winner = d.driver_name
		order by $orderby";

The only addition is the "drivers d" in the from clause of the first select along with the additions to the ending where clause that connects the "drivers" table to the "trk_races" table in that select.

Here is the error I am getting from this change:

Fatal error: Uncaught PDOException: SQLSTATE[42S22]:
Column not found: 1054 Unknown column 'a.race_winner' in 'on clause' 
in /home/albany/public_html/homejg/nascar/nas_show_drivers.php:129 
Stack trace: #0 /home/albany/public_html/homejg/nascar/nas_show_drivers.php(129): 
PDO->query('select a.race_w...') .....

The error column is already being used elsewhere in the query so I can't figure out why I am getting this error.

[Barand]

Use an explicit join ( trk_races JOIN drivers) instead of trk_races,drivers

SELECT  a.race_winner, 
        w.wins,
        SUBSTRING_INDEX(a.race_winner, ' ', -1) AS last_name,
        a.race_name,
        DATE_FORMAT(a.race_date, '%m/%d') AS race_date,
        d.driver_num
FROM trk_races a
    JOIN
    drivers d ON a.season = d.driver_season
          AND a.race_winner = d.driver_name
    LEFT OUTER JOIN
    (
        SELECT race_winner, 
               COUNT(race_date) AS wins 
        FROM trk_races
        WHERE race_winner > '' AND Season=2021
        GROUP BY race_winner
    ) w ON w.race_winner = a.race_winner
WHERE a.race_winner > ''
AND a.Season = 2021


+-------------+------+-----------+-----------+-----------+------------+
| race_winner | wins | last_name | race_name | race_date | driver_num |
+-------------+------+-----------+-----------+-----------+------------+
| G Harrison  |    2 | Harrison  | Race 1    | 01/01     |         38 |
| G Harrison  |    2 | Harrison  | Race 2    | 01/08     |         38 |
| J Lennon    |    1 | Lennon    | Race 3    | 01/15     |         37 |
| R Starr     |    1 | Starr     | Race 4    | 01/22     |         39 |
| P McCartney |    1 | McCartney | Race 5    | 01/29     |         36 |
+-------------+------+-----------+-----------+-----------+------------+

 

[ginerjm]

Once again - thanks Barand.

But - what looked like a very simple addition completely doesn't work.  How come? It is no different than the other join I am doing.

[Barand]

Let it be a lesson always to use explicit join syntax and not FROM A, B

[ginerjm]

But why?  One works and the other doesn't.  Why?  I would never think of this and have not had this issue before.  And - the error message pointed at the join for the outer join that was previously working and was not altered and not at the Where for the newly included table.

Edited April 14, 2022 by ginerjm

[Barand]

I'm guessing the implicit join is screwing with the aliases when it tries to join to the subquery

[kicken]

Quote

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

However, the precedence of the comma operator is less than that of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

source: Mysql Manual - 13.2.10.2 JOIN Clause

Your query would be interpreted as if it were:

select *
from trk_races a
inner join drivers d
	left outer join	(
		select race_winner, count(race_date) as wins from trk_races
		where race_winner > '' and Season='$selyr'
		group by race_winner
	) w
	on w.race_winner = a.race_winner
on 1=1

Essentially your left join becomes like a sub-join of the drivers table.  When you do a join like that you can only reference the parent join table (d) or some same-level sibling table (none here) in your on clauses.  Table a does not meet either of those conditions (it's a sibling of the parent) so it's invalid in your left join's ON clause.

In case it's unclear, when I say sibling tables I mean something like this.

select *
from a
left join b
	inner join c on c.Id=b.cID
	inner join d on d.Id=c.dID
on a.Id=b.aID

Table c is a sibling to table d, so it can be used in the on condition, as can table b as it's the parent table.  Table a cannot because it's the parent's sibling.

 

[ginerjm]

Way more than I ever learned about writing sql.  Way More!!!

Thanks for the lesson.  I'll try to avoid this scenario in the future.