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
source to share
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.
source to share
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
source to share
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
source to share