need guidance with mysql query result

Question

I am have two tables which I have left joined like so below to show me the pads by id added in descending order which works fine.

SELECT p.* FROM ruj_users_pad 
AS p LEFT JOIN ruj_users
AS u ON p.user_id = u.id 
WHERE u.status !=0 
AND 1 
AND p.status = 1 
GROUP BY p.added_date
DESC LIMIT 0, 20

However, now I would like to retrieve a third table which also as an 'added_date' column combine it with the previous query to show the new descending order. The data from the third table is a generated from a button when users click to favorite the current item on the pad.

Here is what I have but it is not working.

SELECT p.*,f.added_date FROM ruj_users_pad
AS p LEFT JOIN ruj_users 
AS u ON b.user_id=u.id 
LEFT JOIN ruj_users_fave 
AS f ON f.brag_id = u.id 
WHERE u.status !=0 
AND 1 
AND p.status = 1 
GROUP BY f.added_date DESC, b.added_date DESC 
LIMIT 0, 20

The result returns the same as the first result. I don't understand what could be wrong. I would like the result to take into consideration that there is an entry in the ruj_users_fave and combine it with the first result to bring the favorited pad to the top.

Help is greatly appreciated.

Why don't you use `ORDER BY`, and, unrelated to your question, what is your intention for writing `AND 1` — s.bandara, Jan 07 '13 at 03:44
@s.bandara because I though using `GROUP BY` would combine the two columns added_date group the results together. I just tried `ORDER BY` but I received the same result. — Claude Grecea, Jan 07 '13 at 03:52
@s.bandara Thanks, but that just returns the items that were favorited in the fave table. I am looking to retrieve all of the pads and the query should look in the favorited table to see if any pads have been favorited and if so use the date of the favorite item to `ORDER BY`. So if a pad was just added but someone favorited a brag from two days ago right after the time the pad was added, that favorited pad should be first in the result because it should use the date when it was favorited to appear in the list. Please let me know if you need more clarification. — Claude Grecea, Jan 07 '13 at 04:21
@s.bandara Here is an easier way to explain this. The query should return the result by the date the pads were added and also take into account the date the pad was favorited and use that date has the new date for the result set. — Claude Grecea, Jan 07 '13 at 04:27

s.bandara · Accepted Answer · 2013-01-10T21:53:27.193

Here is a solution to your problem using UNION to merge two subqueries. It is not the full query of three tables but only the essence with two tables to demonstrate the concept. I use a table pad and a table likes. The first subquery selects the pad's id and its most recent "like" using MAX together with GROUP BY id. Note that I rename the column MAX(liked) to timestamp.

SELECT pad.id, MAX(liked) AS timestamp FROM
pad INNER JOIN likes WHERE likes.pad_id = pad.id
GROUP BY pad.id

The other subquery selects the pad ids that were not liked yet, i.e. no record in the likes table exists for that specific pad id. In this case, you mention that the pad's creation date should be used for determining the order of records returned. Thus, we rename added to timestamp.

SELECT id, added AS timestamp FROM pad
WHERE NOT EXISTS
  (SELECT liked FROM likes WHERE likes.pad_id = pad.id)

We have two subqueries now that return the pad's id and the timestamp so we can combine them to a single result set using UNION, and then ORDER it BY timestamp. The combined query for you looks like this.

SELECT combined.id, combined.timestamp FROM
(
  SELECT pad.id, MAX(liked) AS timestamp FROM
    pad INNER JOIN likes WHERE likes.pad_id = pad.id
    GROUP BY pad.id
  UNION
  SELECT id, added AS timestamp FROM pad
    WHERE NOT EXISTS
      (SELECT liked FROM likes WHERE likes.pad_id = pad.id)
)
combined ORDER BY combined.timestamp DESC;

Thank for you response! I have been wrestling with this for the past two days and I can't get it to do what I want so I am reaching out to you again. Here is my new issue now as a result of your help. I used the code you provide but it doesn't mix the likes with none likes by added date. What it does is it adds the likes at the end of non likes. How can I resolve this? Your help is greatly appreciated. — Claude Grecea, Jan 10 '13 at 21:33
I just edited the combined query by wrapping it in an outer query that will order your results properly. Hope this helps. — s.bandara, Jan 10 '13 at 21:54

need guidance with mysql query result

1 Answers1