Man utd Man utd - 5 months ago 13
MySQL Question

Missing end , Unable to perform cross join mysql

I am trying to do a cross join between two Select statements in mysql.
However, I get an error saying missing end.
However, there is no need of an end statement there

Here is the code

SELECT starts_at, (@row := @row + 1) + start_health_post_id as health_post_id
FROM
(
SELECT DATE_ADD(start_date, INTERVAL start_time MINUTE) AS starts_at
FROM days
CROSS JOIN times
)
slots, (SELECT @row := -1)r
ORDER BY starts_at;

cross join

Select practices.id as practice_id, providers.id as provider_id,
practice_locations.id as practice_location_id
from practices
Inner Join providers on practices.health_post_id = providers.health_post_id

Inner join practice_locations on practices.health_post_id =

practice_locations.health_post_id;

Answer

According to MySQL Manual:

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

I think you need to enclose both queries in ( ):

SELECT t1.*, t2.*
FROM
  (
    SELECT starts_at, (@row := @row + 1) + start_health_post_id as health_post_id
    FROM
    (
        SELECT DATE_ADD(start_date, INTERVAL start_time MINUTE) AS starts_at
        FROM days
        CROSS JOIN times
    ) 
    slots, (SELECT @row := -1) r
    ORDER BY starts_at
  ) as t1
  CROSS JOIN
  (
    Select practices.id as practice_id, providers.id as provider_id, 
            practice_locations.id as practice_location_id
    from practices 
    Inner Join  providers on practices.health_post_id = providers.health_post_id

    Inner join practice_locations on practices.health_post_id =     

    practice_locations.health_post_id
  ) as t2
  ON t1.`t1_field` =  t2.`t2_field` -- change it as you need;
Comments