Jump to content

Trouble with right join


ginerjm
Go to solution Solved by ginerjm,

Recommended Posts

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!

Link to comment
Share on other sites

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

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";    

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.

×
×
  • 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.