Trouble with right join

[ginerjm]

I've done a few join queries in the past, but once again I'm having a bit of a problem setting one up.

 

Here's what I'm trying:

$q = "select r.*, a.*, from workout_results r,
right join (workout_routines x, workout_machines y
where x.user_id = '$userid' and
x.routine_id = '$routine' and
x.machine_id = y.machine_id) a
on r.machine_id = a.machine_id
where r.user_id = '$userid' and
r.workout_date = $seldate and
r.routine_id = '$routine'
order by a.machine_seq";

 

A little insight:

1 - the results table may not have all the items that the routines table does, so I'm trying to use a join so that I can pick up every item in routines, whether there are results or not.

 

2 - the inner query (in the join) works fine as a standalone query - no error there.

3 - all field names have been verified so it's not a typo error.

 

Here's the message i'm getting:

 

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from workout_results r, right join (workout_routines x, workout_machines y ' at line 1' in /home/albany/public_html/homejg/jg/workout/workout_post_results.php:286 Stack trace: #0 /home/albany/public_html/homejg/jg/workout/workout_post_results.php(286): PDO->query('select r.*, a.*...') #1 /home/albany/public_html/homejg/jg/workout/workout_post_results.php(115): BuildPrevGrid('ginerjm', 'Day 1', '20291231') #2 {main} thrown in /home/albany/public_html/homejg/jg/workout/workout_post_results.php on line 286

 

Here's an echo of the actual query statement as well:

 

select r.*, a.*, from workout_results r, right join (workout_routines x, workout_machines y where x.user_id = 'ginerjm' and x.routine_id = 'Day 1' and x.machine_id = y.machine_id) a on r.machine_id = a.machine_id where r.user_id = 'ginerjm' and r.workout_date = 20291231 and r.routine_id = 'Day 1' order by a.machine_seq

 

Appreciate any help from those who can take the time to look at this!

[Barand]

There should not be commas before "from" and before "right join"

 

Queries are more efficient if you use explicit join syntax ie FROM A JOIN B ON ... instead of FROM A,B WHERE

[ginerjm]

I noticed the commas after posting, but since the error was focused later in the query I didn't re-post.

 

Here is the new error message after your change suggestion:

 

for the right syntax to use near '*, y.* from workout_routines x, workout_machines y on x.user_id = 'ginerjm' ' at line 2'

 

(just a snippet)

 

Here is my updated query:

 

$q = "select r.*, a.* from workout_results r
			right join (x.*, y.* from workout_routines x, workout_machines y
				on x.user_id = '$userid' and
					x.routine_id = '$routine' and
					x.machine_id = y.machine_id) a
		where r.user_id = '$userid' and
				r.workout_date = $seldate and
				r.routine_id = '$routine'
		order by a.machine_seq";

Here is an echo of the above:

 

select r.*, a.* from workout_results r right join (x.*, y.* from workout_routines x, workout_machines y on x.user_id = 'ginerjm' and x.routine_id = 'Day 1' and x.machine_id = y.machine_id) a where r.user_id = 'ginerjm' and r.workout_date = 20291231 and r.routine_id = 'Day 1' order by a.machine_seq

[Barand]

Your subquery is missing the word "SELECT"

 

It also has "from workout_routines x, workout_machines y ON"

 

instead of

 

"from workout_routines x INNER JOIN workout_machines y ON"

 

When you join from workout_results to subquery a, you don't specify what you supposed to be joining on?

 

Try

$q = "select r.*, a.* from workout_results r
        right join 
        (
            SELECT x.*, y.* 
            from workout_routines x
            INNER JOIN workout_machines y
                on x.machine_id = y.machine_id
            WHERE x.user_id = '$userid' and
                x.routine_id = '$routine' and
                
        ) a on r.machine_id = a.machine_id and 
            r.user_id = '$userid' and
            r.workout_date = $seldate and
            r.routine_id = '$routine'
        order by a.machine_seq";    

[ginerjm]

You are so right! How the h... did I get so far off the beaten path here I'll never know.

 

I'll play with what you gave me and see what happens. Thx!

[ginerjm]

One minor correction - a hanging 'and' - and it works as desired. Thank you.