CODEWITHSUNDEEP
sqljoinselect
Mischa Mustermann
Mischa Mustermann

Reputation: 91

SQL SELECT and check column from second table

I have two tables in my database. From the Table B i collect some data. I include by using JOIN the data from Table A. In this table there is a column user_id. The user id in table B is the the id from Table A. No i want to get all data from table A but without showing data if the table A id is in the table B column user_id available. I think the trick is the correct usage of JOIN but i need to check the whole column and not just one line.

SQL Query (which is probably not working)

SELECT * FROM tableB
JOIN tableA
ON tableB.user_id = tableA.id
WHERE tableB.user_id != tableA.id

Please see my example i have prepared in Excel: Database table example

Upvotes: 1

Views: 1966

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think I get what you want. You want not exists:

SELECT *
FROM tableB b JOIN
     tableA a
     ON b.user_id = a.id
WHERE NOT EXISTS (SELECT 1
                  FROM tableA a2
                  WHERE b.user_id = a2.id
                 );

EDIT:

I wrote the above, but I didn't fully follow the JOIN conditions. You either seem to want to join on id:

SELECT *
FROM tableB b JOIN
     tableA a
     ON b.id = a.id
WHERE NOT EXISTS (SELECT 1
                  FROM tableA a2
                  WHERE b.user_id = a2.id
                 );

Or no JOIN at all:

SELECT *
FROM tableB b 
WHERE NOT EXISTS (SELECT 1
                  FROM tableA a2
                  WHERE b.user_id = a2.id
                 );

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

To select all the data from tableA where id is not available in tableB you don't need join rather you can use not exists or not in.

I would prefer not exists

Using not exists:

SELECT * from tableA a
WHERE NOT EXISTS (
                    SELECT 1
                    FROM tableB b
                    WHERE b.user_id = a.id
                 );

using not in:

SELECT * from tableA a
WHERE id not in (SELECT user_id FROM tableB );

DB-Fiddle:

 create table TableA (id int, name varchar(50), lastname varchar(50));
 insert into TableA values(1,'john','smith');
 insert into TableA values(2,'Paul','smith');
 
 create table TableB (id int, user_id int, something varchar(50));
 insert into TableB values(1,2,'bla');
 insert into TableB values(2,3,'bla');

Query: (using not exists)

 SELECT * from tableA a
 WHERE NOT EXISTS (
                     SELECT 1
                     FROM tableB b
                     WHERE b.user_id = a.id
                  );

Output:

id name lastname
1 john smith

Query: (using not in)

 SELECT * from tableA 
 WHERE id not in (SELECT user_id  FROM tableB );

Output:

id name lastname
1 john smith

db<fiddle here

Upvotes: 1

Related Questions