Jump to content

Join error


ginerjm
 Share

Go to solution Solved by Barand,

Recommended Posts

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 by ginerjm
Link to comment
Share on other sites

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 by ginerjm
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

  • Solution

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.