Join error

[ginerjm]

Having a problem with a query I am trying to improve with a Join.  Here it is:

$q = "SELECT a.wine_num, a.activity, date_format(a.date_posted,'%m-%d-%y') as date_mdy, 
        	d.activity_desc, a.bottle_cnt, a.my_comments, w.label, w.vintage,
        	CONCAT(s.sort_date, a.wine_num) as sort_col
        FROM mywines_activity a, mywines_activity_codes d, mywines w 
        
        LEFT OUTER JOIN (SELECT wine_num, max(date_posted) as sort_date FROM mywines_activity 
            		GROUP BY wine_num) s
		ON s.wine_num = a.wine_num
        
        WHERE $sel_wine
            a.activity = d.activity_code and
            a.wine_num = w.wine_num
        ORDER BY sort_col DESC";

The error is "a.wine_num" is not a column name.  But - it is!  It is actually one of the keys of table a and it is being using in several relationships here.  The whole query works great without the join.  I'm just to trying to add something to better sequence it.

Edited September 30, 2021 by ginerjm

[Barand]

56 minutes ago, ginerjm said:

I'm just to trying to add something to better sequence it

What sequence do you want?

It usually gives the line number within the query when an SQL error is reported - what is it?

[ginerjm]

Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.wine_num' in 'on clause' in /home/albany/public_html/homejg/wine_activity_review.php:106 Stack trace: #0 /home/albany/public_html/homejg/wine_activity_review.php(106): PDO->prepare('SELECT a.wine_n...') #1 {main} thrown in /home/albany/public_html/homejg/wine_activity_review.php on line 106

Line 106 is the call to prepare

If I alter the first selected field name (the problem one) to an invalid one the error message points at that change.  If I put it back and insert a new invalid name after the 1st, the error message will then point at that.  Why point out a new 2nd field rather than the original 1st field, unless it is NOT invalid.  Curious...

Edited September 30, 2021 by ginerjm

[ginerjm]

Why doesn't this topic show up under the MySql Help forum?  Did I not post it correctly?

[Barand]

You posted in "PHP Coding Help". I moved it to MySQL forum

[ginerjm]

thx

[Barand]

Sorry it's taking a while. I had to create the tables to check the query.

If i structure the FROM clause correctly, using explicit JOINS with ON clauses, the error disappears

FROM mywines_activity a 
             JOIN mywines_activity_codes d ON a.activity = d.activity_code
             JOIN mywines w USING (wine_num)
        
        LEFT OUTER JOIN (SELECT wine_num, max(date_posted) as sort_date FROM mywines_activity 
            		GROUP BY wine_num) s
        ON s.wine_num = a.wine_num

 

[ginerjm]

Correctly?  Does that mean I am doing it incorrectly, despite all of the previous examples I have that worked?

[Barand]

Well, the error disappeared when I did it that way.

• You can only do INNER JOINS with the FROM A, B, C method.
• You have to put the join conditions in the WHERE clause which mixes the table structure with selection criteria
• In my experience, explicit joins are faster.

[ginerjm]

Well following your example here is what I now have:

	    $q = "SELECT a.wine_num, a.activity, date_format(a.date_posted,'%m-%d-%y') as     date_mdy, d.activity_desc, a.bottle_cnt, a.my_comments, w.label, w.vintage,
            s.sort_col
        FROM mywines_activity a
             JOIN mywines_activity_codes d ON a.activity = d.activity_code
             JOIN mywines w USING (wine_num)
	        LEFT OUTER JOIN (SELECT wine_num, CONCAT(max(date_posted), wine_num) as sort_col
            FROM mywines_activity
            GROUP BY wine_num) s
        ON s.wine_num = a.wine_num
	        $sel_wine
	        ORDER BY s.sort_col DESC, a.date_posted";
	

And it works.  Thank you sir.

[Barand]

Are you sure you want that LEFT JOIN and not a normal JOIN? There will always be a match with mywines_activity records.

LEFT JOINS are slow.

[ginerjm]

And I learned something ELSE today!