CODEWITHSUNDEEP
mysqljoin
maknelly
maknelly

Reputation: 131

Joining two separate tables

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

Answers (3)

user1285324
user1285324

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

John Woo
John Woo

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

Teja
Teja

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

Related Questions