Select the rest of the records when one condition is met SQL-CodePudding

I have a user base and the languages assigned to them.

My tables:

users

id | name

lang

id | name

users_lang_user

id  | users_id  | lang_id

I would like to retrieve a user who has at least one record in the relationship database where lang.id = 1, and get their other languages.

SELECT * 
from `users` as users 
JOIN `users_lang_user` as lang 
ON lang.user_id = users.id AND lang.lang_id = '1'

But now I only have where lang id = 1.

How can I get the rest of the user records if this one condition is true

For example, how can I get users where lang id = 12 but also must have record where lang id = 1

CodePudding user response：

If I'm not mistaken, you would like to get the username which has at least two languages and 1 of them must be lang_id 1 , and its associated language name other than the one with the lang_id 1. Wrote and tested this in workbench:

 create table users (id int,name varchar(10));
 insert users values(1,'john'),(2,'mary'),(3,'sarah');
 create table lang (id int,name varchar(10));
 insert lang values(1,'english'),(2,'german'),(3,'maori');
 create table users_lang_user(id int,users_id int,lang_id int);
 insert users_lang_user values(1,1,3),(2,3,1),(3,2,1),(4,2,2);
select u.name,l.name from users u 
join users_lang_user ul
    on u.id=ul.users_id
join lang l
    on ul.lang_id=l.id
where ul.users_id in (select users_id from users_lang_user
                    group by users_id having count(users_id)>1 and min(lang_id)=1)
      and lang_id!=1
;
-- result set:
mary german

Note in this case, as the lang_id 1 happens to be the lowest value of all values ,we can use aggregate function min(lang_id)=1 to cover the lang_id column which is not listed in the group by clause.

CodePudding user response：

One way is

SELECT u.*, l.id langId
FROM `users` u 
JOIN (
   SELECT user_id 
   FROM `users_lang_user` 
   WHERE lang_id = '1') ul1 ON u.id = ul1.user_id
JOIN `users_lang_user` l ON l.user_id = u.id