Pulling player records

Question

I was able to pull the records I wanted but I have a feeling it could be written better for these particular MySQL query strings.

SELECT ID,XAxis,YAxis,Player,Castle,Alliance FROM SC75
WHERE Snap = (SELECT MAX(SavedSnap) FROM DataSnap WHERE Server = 75)
AND hex(Player) IN (hex('Vandiel'))
UNION
SELECT SC.ID,XAxis,YAxis,SC.Player,Castle,Alliance FROM SC75 AS SC
INNER JOIN AltFiller AS AF ON hex(AF.Player) IN (hex('Vandiel'))
WHERE SC.Player = AF.Alts
AND Snap = (SELECT MAX(SavedSnap) FROM DataSnap WHERE Server = 75)
ORDER BY Player,Castle

• Michael Zedeler: This doesn't look too awkward given the scarce information provided, but for starters - why do you have a table named SC75? Does that mean that you also have tables SC74, SC73 and so forth? Does the 75 in SC75 relate to Server = 75? If you posted the schema, I could have some comments.
• sgkdnay: Pretty much and I came across other Database knowledgable person and was pointed out a few flaw but improved, will be posting in a few what was pointed out :)
• Michael Zedeler: Okay, but a broken schema leads to broken queries. This query could potentially be much simpler if you used a better schema. In other words, asking about whether a query "looks right" will usually wind up becoming a discussion about the schema it is using. So you should start out getting the schema right (feel free to post it in another review question here).
• sgkdnay: See below for changes I've made.
• Michael Zedeler: Sorry. I think you're answering a question I didn't ask :) When i write "database schema", I refer to the table layout.

Answer 1

The two parts to the UNION have too much in common. I believe this query is equivalent to yours.

SELECT ID, XAxis, YAxis, Player, Castle, Alliance
 FROM SC75
 WHERE
 Snap = (SELECT MAX(SavedSnap) FROM DataSnap WHERE Server = 75)
 AND
 (
 (hex(Player) IN (hex('Vandiel'))
 OR
 Player IN
 (
 SELECT AF.Alts
 FROM AltFiller AS AF
 WHERE hex(AF.Player) IN (hex('Vandiel'))
 )
 )
 ORDER BY Player, Castle;