SELECT From 2 Tables that not relation

Admin 1
Viewed 71

i have two tables "class" and "account_class (relation table)"

class

id  name        day         time
1   course 1    sunday      08:00
2   course 2    monday      13:00
3   course 3    friday      19:00
4   course 4    saturday    01:00

and account_class

id  username_account        id_class
1   user1                   1
2   user1                   2
3   user2                   3
4   user3                   3

i want to select all class that not selected by user1, i tried left join, right join, inner join, but not working

here's my code

SELECT class.* FROM class WHERE NOT EXISTS (SELECT * FROM account_class WHERE account_class.username_account = 'faraazap')

if the code is correct, it should appear like this

id  name        day         time
3   course 3    friday      19:00
4   course 4    saturday    01:00

Thanks

mariadb mysql sql
4 Answers

I don't think you need any joins if all you wanted to do was get all classes that not selected by user1.

SELECT *
  FROM class
 WHERE id IN (SELECT id_class 
                          FROM account_class
                         WHERE username_account <> 'user1')

But, if you wanted to join tables, and get information from both of them, then :

SELECT c.*, ac.*
  FROM class c JOIN account_class ac ON c.id = ac.id_class
 WHERE ac.username_account <> 'user1'
user_129583190

Your sql

SELECT class.* 
FROM class 
WHERE NOT EXISTS 
  (SELECT * 
   FROM account_class 
   WHERE account_class.username_account = 'faraazap')

ends up returning all classes when this user is not registered in any, and no classes when the user is not registered in any. This is because your NOT EXISTS is not connecting the class being queried to the classes being attended. A slight modification is necessary:

SELECT c.* 
FROM class c
WHERE c.Id not in  
  (SELECT id_class
   FROM account_class 
   WHERE account_class.username_account = 'faraazap')

With this query, you are now selecting all classes where the class Id value does not appear in the list of classes attended by this user.

user_510

I wasn't sure if you wanted to use faraazap or user1 in your comparison. But you can solve this with an "IN" statement, shown below. Where you only select records of table "class" where its ID is in another select statement.

SELECT * 
FROM class
WHERE class.id NOT IN (
  SELECT id_class 
  FROM account_class 
  WHERE username_account = 'user1'
)
user_71258860 
select * from class 
where id not in
(select class.id 
from class, account_class
where class.id=id_class
and username_account='user 1');
user_150542680
Related