SQL expression problem

Question

Below is my table, a User could have multiple profiles in certain languages, non-English profiles have a higher priority.

+----------+--------+----------------+----------------+
|ProfileID |UserID  |ProfileLanguage |ProfilePriority |
+----------+--------+----------------+----------------+
|1         |1       |en-US           |2               |
+----------+--------+----------------+----------------+
|2         |1       |es-MX           |1               |
+----------+--------+----------------+----------------+
|3         |1       |ja-JP           |1               |
+----------+--------+----------------+----------------+
|4         |2       |es-MX           |1               |
+----------+--------+----------------+----------------+
|5         |2       |ja-JP           |2               |
+----------+--------+----------------+----------------+
|6         |2       |de-DE           |1               |
+----------+--------+----------------+----------------+
|7         |3       |en-US           |2               |
+----------+--------+----------------+----------------+

For example: When a Spanish-speaking visitor requests my site (where ProfileLanguage = 'es-MX' or ProfilePriority = 2), I want the records like below:

+----------+--------+----------------+----------------+
|ProfileID |UserID  |ProfileLanguage |ProfilePriority |
+----------+--------+----------------+----------------+
|2         |1       |es-MX           |1               |
+----------+--------+----------------+----------------+
|5         |2       |ja-JP           |2               |
+----------+--------+----------------+----------------+
|7         |3       |en-US           |2               |
+----------+--------+----------------+----------------+

Below, is the basic SQL to get the users:

SELECT UserID, MIN(ProfilePriority) AS ProfilePriority
FROM Profile
WHERE ProfileLanguage = 'es-MX' OR ProfilePriority = 2
GROUP BY UserID

But as you know, I can only get the UserID, but I also need other column information, like ProfileID etc.. So I hope experts here could tell me the correct SQL expression to get the right records.

Answer 1

SELECT *
FROM Profile as tb1 inner join
(SELECT UserID, MIN(ProfilePriority) AS ProfilePriority
FROM Profile
WHERE ProfileLanguage = 'es-MX' OR ProfilePriority = 2
GROUP BY UserID) as tb2 on 
tb1.userid = tb2.userid and tb1.ProfilePriority = tb2.ProfilePriority

Enter all the columns you require in separated with a comma instead of * in the above query.

Answer 2

This may work, if profilepriority and userid could be a composite unique key;

select p.* from Profile p join 
(SELECT UserID, MIN(ProfilePriority) AS ProfilePriority
FROM Profile
WHERE ProfileLanguage = 'en-US' OR ProfilePriority = 2
GROUP BY UserID) tt
on p.userID = tt.UserID and p.ProfilePriority = tt.ProfilePriority

Answer 3

It seems to me, though it is not clear without your table and maybe some sample data, that your problem is solved by just extending your grouping as follows:

SELECT UserID, ProfileID, MIN(ProfilePriority) AS ProfilePriority
FROM Profile
WHERE ProfileLanguage = 'en-US' OR ProfilePriority = 2
GROUP BY UserID, ProfileID

Answer 4

I guess you have a priority if profilepriority and profilelanguage both are correct. So this may help in that case;

select p.* from
(select tt1.userid, min(tt.pr) pr from 
(
SELECT profileid, UserID, MIN(ProfilePriority) AS ProfilePriority, 1 as pr
FROM Profile
WHERE ProfileLanguage = 'en-US' and ProfilePriority = 2
GROUP BY profileid, UserID

union 

SELECT profileid, UserID, MIN(ProfilePriority) AS ProfilePriority, 2 as pr
FROM Profile
WHERE ProfileLanguage = 'en-US' or ProfilePriority = 2
GROUP BY profileid, UserID

) tt1
group by userid) tt2    
join
(
SELECT profileid, UserID, MIN(ProfilePriority) AS ProfilePriority, 1 as pr
FROM Profile
WHERE ProfileLanguage = 'en-US' and ProfilePriority = 2
GROUP BY profileid, UserID

union 

SELECT profileid, UserID, MIN(ProfilePriority) AS ProfilePriority, 2 as pr
FROM Profile
WHERE ProfileLanguage = 'en-US' or ProfilePriority = 2
GROUP BY profileid, UserID
) tt3
on tt2.userId = tt3.userId and tt2.pr = tt3.pr
join profile p on p.profileid = tt3.profileid