CODEWITHSUNDEEP
mysql
user418775
user418775

Reputation: 698

MYSQL query that orders data by timestamps contained in two tables

I'm trying to build a query that will order results by date and time, but the date and time upon which I want to base the ordering is contained in two tables. Additionally, I'd like to group the data by loc_id for rows having the same date.

For example, if I had this data:

enter image description here

Notes:
- parent_id links to the id in TABLE A.
- there are two other tables USER and LOC which are used to get the user name and location name.
- comments are optional in TABLE A, but will always exist in TABLE B.

I've found a lot of info on this site that's close to what I need, but nothing that's quite helped me solve this issue.

Thanks.

Upvotes: 2

Views: 207

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

SELECT a.`date`
     , a.`time`
     , l.loc_name AS l_name
     , a.rating
     , a.comment
     , u.user_name
FROM table_a AS a
  LEFT JOIN loc AS l 
    ON l.loc_id = a.loc_id
  LEFT JOIN `user` AS u
    ON u.user_id = a.user_id

UNION ALL

SELECT b.`date`
     , b.`time`
     , l.loc_name AS l_name
     , NULL AS rating
     , b.comment
     , u.user_name
FROM table_b AS b
  LEFT JOIN table_a AS a
    ON a.id = b.parent_id
  LEFT JOIN loc AS l 
    ON l.loc_id = a.loc_id
  LEFT JOIN `user` AS u
    ON u.user_id = b.user_id

ORDER BY `date`
       , l_name
       , `time`

Upvotes: 3

Jules
Jules

Reputation: 7213

Something like this?

(Assumed your users table was called users and your locations table locations.)

SELECT a.date, a.time, l.loc_name, a.rating, b.comment, u.user_name
FROM table_a a
JOIN locations l ON l.loc_id = a.loc_id
LEFT JOIN table_b b ON b.parent_id = a.id
RIGHT JOIN users u ON a.user_id = u.user_id

GROUP BY a.loc_id

ORDER BY a.date DESC, a.time DESC

Upvotes: 0

Related Questions