CODEWITHSUNDEEP
sqlmysql
djburdick
djburdick

Reputation: 11940

mysql multiple where and inner join query combination

I'm a little lost as how to run two mysql queries as one(return one result set to be sorted etc).

I understand how to do multiple JOINS but need to combine the below which is more than just a multiple join - it would include a multiple where etc.

1st query

     sql = "SELECT s.id, s.song_name
           FROM `songs` as s
           INNER JOIN `artists` as a ON s.artist_id = a.id
           WHERE ((`a`.id = #{search}))"

2nd query

   sql = "SELECT s.id, s.song_name
      FROM `songs` as s
      INNER JOIN `similarments` as si ON s.artist_id = si.artist_id
      WHERE ((`si`.similar_id = #{search}))"

And then run both queries at once so I can ORDER them etc. Or combine them as one big query (maybe put an OR somewhere)?

Thanks!

Upvotes: 0

Views: 641

Answers (1)

Mark Byers
Mark Byers

Reputation: 837996

The simple way to run two queries and combine the results is to use UNION (or UNION ALL if you don't wish to remove duplicates). In your case it would look like this:

(
    SELECT s.id, s.song_name
    FROM `songs` as s
    INNER JOIN `artists` as a ON s.artist_id = a.id
    WHERE ((`a`.id = #{search}))
)
UNION
(
    SELECT s.id, s.song_name
    FROM `songs` as s
    INNER JOIN `similarments` as si ON s.artist_id = si.artist_id
    WHERE ((`si`.similar_id = #{search}))
)
ORDER BY ....

Upvotes: 4

Related Questions