Can I ask MySQL to do a "double" search?

Question

I was wondering if this is possible to make MySQL do a "double search"; I don't really know how to say it well so here's an example:

I have a table of songs information (title,album, etc etc ...). I want to get the next song in the album. I have the ID of the current song. Currently I get the row, check the album and track field, then do another MySQL query based on album field and check if there is another track after the current one.

Could I, in one query, tell MySQL that I want all rows of the album contained in the row of the id I have?

Since I really don't know if this is possible or how to describe it, I didn't find anything useful on Stack Overflow, so sorry if there is already something similar.

Pretty vague description that naturally leads to many different types of answers bellow. What exactly are you trying to achieve? You have an ID and you want to get the record for that ID and all the subsequent (next) ones? If so you could easily use the `WHERE id > given id` syntax but still the question is not specific enough. — , Dec 23 '12 at 10:07
That's unfair @holodoc, the OPs native language is not English and I think they communicated what they were after pretty well. The two answers can be functionally equivalent as there are often many ways of doing the same thing in SQL. This isn't a reflection on the OP either. — Ben, Dec 23 '12 at 10:12
@holodoc , i should have mentionned it but the id is not relevant to the order of tracks , it's juste the primary key in the table. But i totally agree that i could have been more specific but like said Ben , my native language is not english and i got some difficulties to explain it very well and i'm sorry for that — eephyne, Dec 23 '12 at 10:24
@Ben Which part of my comment was "unfair"? o_O I just said that the way the question was formulated it leaves too much room for speculation about what the OP actually wants. Nothing more, nothing less. As for the language part English is far from being my native language too but I don't see how that part is even relevant to the discussion. — , Dec 23 '12 at 10:28

Ben · Accepted Answer · 2012-12-23T10:36:21.433

5

You could use a JOIN to get what you want. Assuming your table has an ID that links to an album something like this would work to get everything that comes after the track currently being listened to.

select *
  from songs a
  join songs b
    on a.album_id = b.album_id
 where a.song_id = 1 
   and b.song_id > a.song_id

Obviously, if you want everything in the album then remove the second condition. If you want to include the track being listened to change > to >=.

You've just commented:

i should have mentionned it but the id is not relevant to the order of tracks , it's juste the primary key in the table.

If this is the case then then b.song_id > a.song_id is probably incorrect. You haven't given us your full schema but I would highly recommend if your songs table contained the order the tracks should be played in, so it would look something like (SONG_ID, ALBUM_ID, ALBUM_ORDER, ... more song level info ). Your query would then become:

select *
  from songs a
  join songs b
    on a.album_id = b.album_id
 where a.song_id = 1 
   and b.album_order > a.album_order

edited Dec 23 '12 at 10:36

answered Dec 23 '12 at 10:04

Ben

51,770
36
127
149

for what i saw this far (just looked at some docs) JOIN seem more powerful than the subquery , but do you know wich one is more efficient in term of speed and server load ? – eephyne Dec 23 '12 at 10:31
You may want to look at http://stackoverflow.com/questions/2577174/join-vs-subquery – luiges90 Dec 23 '12 at 10:33
3

They do different things @eephyne. The only way to be sure is to test on your own environment. If they're about the same speed (and they probably will be) use the thing that _you_ understand most. – Ben Dec 23 '12 at 10:33
after more digging and test , join doesn't seem to be what i need , since the two tables are the same and join merge them , i got X rows with twice each columns and i don't find it very readable. But i learn the utility of join and certainly use id later , thanks for that :p – eephyne Dec 23 '12 at 10:58
well, finally because i need at least four subqueries when i use them it's very slow , so I searched how to remove duplicate with JOIN. I found that is simply by replacing SELECT * by SELECT b.* . JOIN will definitely do a better job for me , thanks a lot – eephyne Dec 25 '12 at 07:32

luiges90 · Answer 2 · 2012-12-23T10:10:18.193

It looks like you need subqueries

SELECT * FROM song_table WHERE album = 
    (SELECT album FROM song_table WHERE id = 12)

This query get all rows that share the same album with the row having id = 12

If your subquery may return more than one row, you need IN:

SELECT * FROM song_table WHERE album IN 
    (SELECT album FROM song_table WHERE genre = "Techno")

This query get all rows that have an album which (the album) contains genre = "Techno" (All songs in any album that contains (at least one) Techno songs)

score 0 · Answer 3 · answered Dec 23 '12 at 10:31

Currently I get the row, check the album and track field, then do another MySQL query based on album field and check if there is another track after the current one.

What's the problem with it? Is it slow, or inconvenient, or too complicated?
Do you have any practical reason to look for another solution?

I was wondering if this is possible to make MySQL do a "double search"

Even if there is a positive answer, not everything that is "possible" deserves an implementation.

Your current setup is plain and simple, you can maintain it yourself.
While every proposed solution being complex, require more knowledge to maintain and may lead to some problems, as Mysql have difficulties with interpreting complex queries and easily make things worse.

so for you , it's better to do two query and process them in the client that doing only one complex query to the mysql server? I agree for the simplicity of maintaining the code but in term of server load is this better? — eephyne, Dec 23 '12 at 10:34

Can I ask MySQL to do a "double" search?

3 Answers3