Reputation: 131
How would I join Table 1 and 2 together?
Table 1:
SELECT Roomid, Room, Latitude, Longitude,
ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) AS D
FROM Rooms
AS t1
WHERE Latitude>(:minLat) AND Latitude<(:maxLat)
AND Longitude>(:minLon) AND Longitude<(:maxLon)
AND ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) < (:rad)
ORDER BY D
limit 6
Table 2:
SELECT name, date, roomid, descr
FROM Events
AS t2
WHERE date > NOW()
GROUP BY roomid
ORDER BY date
Whenever I try to perform a JOIN on t1.Roomid = t2.roomid
it does not work.
Upvotes: 0
Views: 145
Reputation:
@Maknelly: Why this join is not working for you, first of all give your own code which are you trying for join.......then we can further help you in this concern.
Upvotes: 0
Reputation: 263893
try this one:
SELECT *
FROM
(
SELECT Roomid,
Room,
Latitude,
Longitude,
ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) AS D
FROM Rooms
WHERE Latitude>(:minLat) AND
Latitude<(:maxLat) AND
Longitude>(:minLon) AND
Longitude<(:maxLon) AND
ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) < (:rad)
) t1
INNER JOIN
(
SELECT `name`, `date`, roomid, `descr`
FROM `Events`
WHERE `date` > NOW()
GROUP BY roomid
) t2
ON t1.RoomID = t2.RoomID
ORDER BY t1.D, t2.`Date`
Upvotes: 1
Reputation: 13544
SELECT * FROM
(
SELECT Roomid, Room, Latitude, Longitude,
ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) AS D
FROM Rooms
AS t1
WHERE Latitude>(:minLat) AND Latitude<(:maxLat)
AND Longitude>(:minLon) AND Longitude<(:maxLon)
AND ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) < (:rad)
ORDER BY D
limit 6
) A,
(
SELECT name, date, roomid, descr
FROM Events
AS t2
WHERE date > NOW()
GROUP BY roomid
ORDER BY date
) B
WHERE A.Roomid = B.roomid;
Upvotes: 0