0

I have 2 tables, trophies_list and trophies_won. I want to list all trophies that have been won first, then all trophies that have not been won in a random order. I've searched all over but with no luck.

My tables...

trophies_won:

trophy_id
winner_id

trophies_list:

trophy_id
trophy_name

Just to clarify, i want all the trophies listed, but with the ones that appear in 'trophies_won' and with the chosen 'winners_id' to be shown first, then the rest in random order.

john
  • 1,280
  • 1
  • 18
  • 48

1 Answers1

1

You need an outer join.

If you don't care about the order in which the "won" trophies appear, you could very simply do the following (because winner_id will be NULL for those trophies that have not been won and NULL is ordered "after" all other results in a descending order):

SELECT * FROM
  trophies_list
  NATURAL LEFT JOIN trophies_won
ORDER BY winner_id DESC, RAND();

Of course, if when you said that "all trophies that have not been won should be in a random order" you meant you merely didn't care about the order, you can omit the trailing ", RAND()".

UPDATE

In response to your comments, I think you're actually looking for something more like:

SELECT trophies_list.*, NOT ISNULL(trophies_won.winner_id) AS won FROM
  trophies_list
  LEFT JOIN trophies_won ON (
        trophies_won.trophy_id = trophies_list.trophy_id
    AND trophies_won.winner_id = 43
  )
ORDER BY won DESC, RAND();

By performing an outer join ON the criteria you're after, you can identify amongst the list of all trophies which match that criteria and which don't. In this case, which trophies have been won by the specified winner and which haven't.

The ORDER BY clause then ensures that the results are ordered with those trophies that have been won appearing first, followed by those that have not; within those tiers, the order is randomised (again, if you simply don't care about the order you can remove the , RAND() part which is adding unnecessary overhead).

Community
  • 1
  • 1
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Thanks, but is there any way i can only get those from 'trophies_won' with a specific winners_id? I've tried adding 'WHERE winners_id = 43' (for example) in a few places, but this doesn't seem to work. – john Apr 23 '12 at 00:45
  • @johnwilliams: Edited to reflect this additional stipulation. – eggyal Apr 23 '12 at 00:49
  • when i try it now, it all works fine when the winners_id in the WHERE clause is found, but if it's not found, that trophy in the list doesn't show up at all. Say if the winners_id is not 43, but it has another value, it doesn't get included in the selection. – john Apr 23 '12 at 01:24
  • @johnwilliams: I'm not sure I understand. Say trophy 1 has been won by winner 43, 2 by winner 78 and trophy 3 has not yet been won at all. From your previous comment, I was under the impression that you wanted to retrieve all trophies won by winner 43 (in this example, just trophy 1) and any not yet won by anyone (3); similarly if changing `43` in the query above to `78`, one would wish to retrieve all trophies won by that winner (i.e. trophy 2) and any not yet wone by anyone (3). Is this not what you're after, or does the above query not achieve those results? – eggyal Apr 23 '12 at 07:14
  • I need to retrieve all trophies won by winner 43 (in this example, just trophy 1) and any not yet won by that winner. So for whichever winner i pick i want to display all the possible trophies, but the ones that have been won by that user are shown first. Trophies can be won by more than one person. Thanks for your help on this btw – john Apr 23 '12 at 08:34
  • @johnwilliams: see above; you should probably also revise your question to be more clear that this was what you were after. – eggyal Apr 23 '12 at 08:56
  • Perfect, Thank you! Just for anyone else wanting the script IS_NULL should be the ISNULL function. – john Apr 23 '12 at 09:20