mysql multiple where and inner join query combination

Question

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!

LEFT JOIN to both tables in a single query from SONGs...

OMG Ponies
– OMG Ponies

2010-05-11 22:23:38 +00:00
Commented May 11, 2010 at 22:23 — OMG Ponies
– OMG Ponies, Commented May 11, 2010 at 22:23

Mark Byers · Accepted Answer · 2010-05-11 22:21:15Z

4

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 ....

answered May 11, 2010 at 22:21

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql multiple where and inner join query combination

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related