SQL expression problem
Below my table, a user can have multiple profiles in specific languages, and 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 need 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 for 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 information about the columns, like ProfileID, etc. So I hope the experts here can tell me the correct SQL expression to get the records I want.
This can work if the profilepriority and userid can 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
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 required columns, separated by commas, not * in the above query.
It seems to me, although without your table and maybe some sample data, it's not clear that your problem is solved by extending your grouping like this:
SELECT UserID, ProfileID, MIN(ProfilePriority) AS ProfilePriority
FROM Profile
WHERE ProfileLanguage = 'en-US' OR ProfilePriority = 2
GROUP BY UserID, ProfileID
I think you have priority if profilepriority and profilelanguage are both correct. So it can help in this 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