ginerjm Posted March 29, 2016 Share Posted March 29, 2016 (edited) Not my first time doing this but I'm missing something today. Gonna really hate it when you point out my problem here. My query: $q = "select date_format(a.Match_date,'%m/%d/%y') as Match_date, a.Team_no, a.Player1, a.Player2, b.Last_Name as p1ln, b.First_name as p1fn, c.Last_name as p2ln, c.First_name as p2fn from dart_matches a, voorhees_data.MMS_Members as b, voorhees_data.MMS_Members as c left outer join (select Player_no as scored1 from dart_match_scores where Match_date = $qdate) as s on a.Player1 = scored1 left outer join (select Player_no as scored2 from dart_match_scores where Match_date = $qdate) as t on a.Player2 = scored2 where a.Player1 = b.Roster_no and a.Player2 = c.Roster_no and a.Match_date = $qdate order by a.Team_no"; The error I keep getting is: Column not found: 1054 Unknown column 'a.Player1' in 'on clause' The thing is my table structure clearly shows the existence of Player1 in my table and have prefixed every occurrence of it in the query, there are no duplicates and yet this is where it keeps stopping no matter what I do. Thanks in advance! Edited March 29, 2016 by ginerjm Quote Link to comment Share on other sites More sharing options...
Barand Posted March 29, 2016 Share Posted March 29, 2016 A confusing mix of JOIN syntaxes in that query. Any chance of a test dump of those three tables? Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 29, 2016 Author Share Posted March 29, 2016 Heres the one that is giving me the problem: Array ( [Field] => Match_date [Type] => date [Null] => NO [Key] => PRI [Default] => [Extra] => ) Array ( [Field] => Team_no [Type] => int(11) [Null] => NO [Key] => PRI [Default] => [Extra] => ) Array ( [Field] => Player1 [Type] => varchar(3) [Null] => NO [Key] => [Default] => [Extra] => ) Array ( [Field] => Player2 [Type] => varchar(3) [Null] => NO [Key] => [Default] => [Extra] => ) Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 29, 2016 Share Posted March 29, 2016 (edited) What is even the purpose of the LEFT OUTER JOINs with the sub-queries? You are only selecting one value from those sub-queries and it is a value that you already have (the player ID). EDiT: I would assume you want the scores from that table - but. that isn't what you are selecting Also, I would highly suggest giving your table aliases meaningful aliases instead of a, b, c, etc. Edited March 29, 2016 by Psycho Quote Link to comment Share on other sites More sharing options...
Solution Psycho Posted March 29, 2016 Solution Share Posted March 29, 2016 (edited) Give this a try. I guessed on the field name 'score' in the select list as the field name from the dart_match_scores table. I would also state that using the match date as the foreign key is a bad idea. I would suggest having a unique identifier in the dart_matches table and using that as the foreign key in any associated tables. As it stands now, a player could not have two matches on the same date. That may be something that shouldn't occur, but the database structure shouldn't be dependent on it. SELECT date_format(m.Match_date,'%m/%d/%y') as Match_date, m.Team_no, m.Player1, p1.Last_Name as p1ln, p1.First_name as p1fn, s1.score, m.Player2, p2.Last_name as p2ln, p2.First_name as p2fn, s2.score FROM dart_matches m -- JOIN to get player 1 name data JOIN voorhees_data.MMS_Members as p1 ON m.Player1 = p1.Roster_no -- JOIN to get player 1 score data LEFT OUTER JOIN dart_match_scores AS s1 ON m.Player1 = s1.Player_no AND s1.Match_date = m.Match_date -- JOIN to get player 2 name data JOIN voorhees_data.MMS_Members as p2 ON m.Player2 = p2.Roster_no -- JOIN to get player 2 score data LEFT OUTER JOIN dart_match_scores AS s1 ON m.Player2 = s2.Player_no AND s2.Match_date = m.Match_date WHERE m.Match_date = $qdate ORDER BY m.Team_no Edited March 29, 2016 by Psycho Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 29, 2016 Author Share Posted March 29, 2016 Interesting... minor syntax changes and it works. Funny I've used the methods employed here bedore but this time there must be something unique about it. Here's my now-working code: $q = "select date_format(a.Match_date,'%m/%d/%y') as Match_date, a.Team_no, a.Player1, a.Player2, b.Last_Name as p1ln, b.First_name as p1fn, c.Last_name as p2ln, c.First_name as p2fn, s1.Player_no as scored1, s2.Player_no as scored2 from dart_matches as a join voorhees_data.MMS_Members as b on a.Player1 = b.Roster_no join voorhees_data.MMS_Members as c on a.Player2 = c.Roster_no left outer join dart_match_scores as s1 on s1.Player_no = a.Player1 and s1.Match_date = $qdate left outer join dart_match_scores as s2 on s2.Player_no = a.Player2 and s2.Match_date = $qdate where a.Match_date = $qdate order by a.Team_no"; Thank you very much! Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 29, 2016 Share Posted March 29, 2016 left outer join dart_match_scores as s1 on s1.Player_no = a.Player1 and s1.Match_date = $qdate left outer join dart_match_scores as s2 on s2.Player_no = a.Player2 and s2.Match_date = $qdate where a.Match_date = $qdate The variable $qdate in the JOIN criteria for those two tables is unnecessary and only complicates the query. Since the date in the "Match_date" table needs to be the same as in the "dart_matches" table you should just do this left outer join dart_match_scores as s1 on s1.Player_no = a.Player1 and s1.Match_date = a.Match_date left outer join dart_match_scores as s2 on s2.Player_no = a.Player2 and s2.Match_date = a.Match_date where a.Match_date = $qdate But, again, the match date is a poor choice for associating records. It should only exist in the dart_matches table and that table should have a unique ID field to be used as a foreign key in these associated tables. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 29, 2016 Author Share Posted March 29, 2016 While what you say makes sense, it is a good constraint for this appl since a player can only play one match a night. Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 30, 2016 Share Posted March 30, 2016 While what you say makes sense, it is a good constraint for this appl since a player can only play one match a night. That why you would put a unique constraint on the dart_matches table. Since you are using the date to associate records, I suppose you could index the date fields. Never used date fields for indexing, but if you are going to use it for JOINing (as opposed to an INT field) then you should index it. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.