Join difficulty

[ginerjm]

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

[Barand]

A confusing mix of JOIN syntaxes in that query.

 

Any chance of a test dump of those three tables?

[ginerjm]

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] =>

)

[Psycho]

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

[Psycho]

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

[ginerjm]

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!

[Psycho]

 

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.

[ginerjm]

While what you say makes sense, it is a good constraint for this appl since a player can only play one match a night.

[Psycho]

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.