Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

php - Combining two queries into one

I have two type of message, one is private message and another is credit message are inserting into two different table. Now I'm trying to fetch the data.

SELECT * ,(SELECT COUNT(*) 
           FROM votes 
           WHERE message_id = m.message_id
             AND vote_type = 'like') AS likes,
          (SELECT COUNT(*) 
           FROM votes
           WHERE message_id = m.message_id 
           AND vote_type = 'dislike') AS dislikes 
FROM messages m
WHERE 1 #and hidden is null
  and recipient_id = 1
ORDER BY datetime DESC

and

SELECT * ,(SELECT COUNT(*) 
           FROM votes 
           WHERE message_id = m.message_id
             AND vote_type = 'like') AS likes,
          (SELECT COUNT(*)
           FROM votes
           WHERE message_id = m.message_id  
             AND vote_type = 'dislike') AS dislikes 
FROM private_messages m 
WHERE 1 #and hidden is null 
  and recipient_id = 1 
ORDER BY datetime DESC

Now want to merge them into one query one extra parameter will show its private message or credit message.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

If the existing queries do what you want/need, UNION will make it pretty simple to combine them, something like;

SELECT * FROM (
    SELECT is_private 0, <field1>,<field2>,<field3>, ... ,(SELECT COUNT(*) 
               FROM votes 
               WHERE message_id = m.message_id
                 AND vote_type = 'like') AS likes,
              (SELECT COUNT(*) 
               FROM votes
               WHERE message_id = m.message_id 
               AND vote_type = 'dislike') AS dislikes 
    FROM messages m
    WHERE 1 #and hidden is null
      and recipient_id = 1
    UNION ALL
    SELECT 1, <field1>, <field2>, <field3>, ... ,(SELECT COUNT(*) 
               FROM votes 
               WHERE message_id = m.message_id
                 AND vote_type = 'like') AS likes,
              (SELECT COUNT(*)
               FROM votes
               WHERE message_id = m.message_id  
                 AND vote_type = 'dislike') AS dislikes 
    FROM private_messages m 
    WHERE 1 #and hidden is null 
      and recipient_id = 1 
)
ORDER BY datetime DESC

Note that you need to select the same number/order of columns from both queries for the union to work. SELECT * makes it hard to verify if/that that is the case. If


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...