CODEWITHSUNDEEP
mysql
Walter
Walter

Reputation: 71

Combining two different queries

I have two queries that select similar columns, but have different WHERE and JOIN clauses. I need to combine these two queries with UNION so that in total it shows 26 results (combined), sorted by date. I have no idea how I would do this with the following two queries.

First query:

SELECT p.post_id,
       p.reply_to,
       p.parent_id,
       p.post_path,
       p.user_id,
       p.content,
       p.datetime,
       p.total_likes,
       p.total_replies,
       p.total_reposts,
       u.username,
       u.display_name,
       l.like_id,
       l.user_id
FROM posts p 
LEFT JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON (l.post_id = p.post_id AND l.user_id = ?)
WHERE p.user_id IN (SELECT following_id FROM follows WHERE user_id = ?)
AND p.removed != 1
ORDER BY p.datetime DESC LIMIT 26

Second query:

SELECT p.post_id,
       p.reply_to,
       p.parent_id,
       p.post_path,
       p.user_id,
       p.content,
       p.datetime,
       p.total_likes,
       p.total_replies,
       p.total_reposts,
       u.username,
       u.display_name,
       l.like_id,
       l.user_id
FROM posts p
LEFT JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON l.post_id = p.post_id
WHERE l.user_id IN (SELECT user_id FROM likes WHERE user_id IN (SELECT following_id FROM follows WHERE user_id = ? AND following_id != ?))
AND l.user_id != p.user_id AND p.removed != 1
ORDER BY p.datetime DESC LIMIT 26

How would I do this?

Upvotes: 0

Views: 74

Answers (3)

trogdor
trogdor

Reputation: 1656

If the selected columns have compatible types, just put them together with UNION. I understand you want to put the LIMITat the end to apply it to the complete query:

 SELECT p.post_id,
   p.reply_to,
   p.parent_id,
   p.post_path,
   p.user_id,
   p.content,
   p.datetime,
   p.total_likes,
   p.total_replies,
   p.total_reposts,
   u.username,
   u.display_name,
   l.like_id,
   l.user_id
FROM posts p 
   LEFT JOIN users u ON p.user_id = u.user_id
   LEFT JOIN likes l ON (l.post_id = p.post_id AND l.user_id = ?)
WHERE p.user_id IN (SELECT following_id FROM follows WHERE user_id = ?)
   AND p.removed != 1
UNION
SELECT p.post_id,
   p.reply_to,
   p.parent_id,
   p.post_path,
   p.user_id,
   p.content,
   p.datetime,
   p.total_likes,
   p.total_replies,
   p.total_reposts,
   u.username,
   u.display_name,
   l.like_id,
   l.user_id
FROM posts p
LEFT JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON l.post_id = p.post_id
WHERE l.user_id IN (SELECT user_id FROM likes WHERE user_id IN 
          (SELECT following_id FROM follws WHERE user_id = ? AND following_id != ?))
    AND l.user_id != p.user_id AND p.removed != 1
ORDER BY p.datetime DESC LIMIT 26

Upvotes: 0

Victorino
Victorino

Reputation: 1641

you can use UNION . try this 

SELECT   
  (SELECT p.post_id,
     p.reply_to,
     p.parent_id,
     p.post_path,
     p.user_id,
     p.content,
     p.datetime,
     p.total_likes,
     p.total_replies,
     p.total_reposts,
     u.username,
     u.display_name,
     l.like_id,
     l.user_id
  FROM posts p 
  LEFT JOIN users u ON p.user_id = u.user_id
  LEFT JOIN likes l ON (l.post_id = p.post_id AND l.user_id = ?)
  WHERE p.user_id IN (SELECT following_id FROM follows WHERE user_id = ?)
  AND p.removed != 1
  ORDER BY p.datetime DESC LIMIT 26)

UNION

 (SELECT p.post_id,
      p.reply_to,
      p.parent_id,
      p.post_path,
      p.user_id,
      p.content,
      p.datetime,
      p.total_likes,
      p.total_replies,
      p.total_reposts,
      u.username,
      u.display_name,
      l.like_id,
      l.user_id
FROM posts p
LEFT JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON l.post_id = p.post_id
WHERE l.user_id IN (SELECT user_id FROM likes WHERE user_id IN (SELECT following_id FROM   follows WHERE user_id = ? AND following_id != ?))
AND l.user_id != p.user_id AND p.removed != 1
ORDER BY p.datetime DESC LIMIT 26)

Upvotes: 0

b.runyon
b.runyon

Reputation: 154

You should be able to put a UNION or UNION ALL between the two select statements. Also, make sure you remove the first ORDER BY, that will throw an error. The below statement should run.

SELECT p.post_id,
       p.reply_to,
       p.parent_id,
       p.post_path,
       p.user_id,
       p.content,
       p.datetime,
       p.total_likes,
       p.total_replies,
       p.total_reposts,
       u.username,
       u.display_name,
       l.like_id,
       l.user_id
FROM posts p 
LEFT JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON (l.post_id = p.post_id AND l.user_id = ?)
WHERE p.user_id IN (SELECT following_id FROM follows WHERE user_id = ?)
AND p.removed != 1
UNION
SELECT p.post_id,
       p.reply_to,
       p.parent_id,
       p.post_path,
       p.user_id,
       p.content,
       p.datetime,
       p.total_likes,
       p.total_replies,
       p.total_reposts,
       u.username,
       u.display_name,
       l.like_id,
       l.user_id
FROM posts p
LEFT JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON l.post_id = p.post_id
WHERE l.user_id IN (SELECT user_id FROM likes WHERE user_id IN (SELECT following_id FROM follows WHERE user_id = ? AND following_id != ?))
AND l.user_id != p.user_id AND p.removed != 1
ORDER BY p.datetime DESC LIMIT 26

Upvotes: 1

Related Questions