CODEWITHSUNDEEP
sql
Jessica Williams
Jessica Williams

Reputation: 356

SQL query returning all rows from Table2

I am trying to join 2 tables and return data if the Table1.codeId is presented on Table2 OR if Table1.codeId = 0. However, It retrieves the data from Table2.

Table1 {
   name nvarchar,
   codeId int
 }

|  name  | codeId |
|--------|--------|
|  Bob   |    1   |
|  Bob   |    2   |
|  Chris |    0   |
   

Table2 {
   id int,
   codeName nvarchar
 }

|  id  | codeName |
|------|----------|
|  1   | Engineer |
|  2   | Doctor   |
|  3   | Dentist  |
|  4   | Pilot    |
|  5   | Mechanic |
   

SELECT t1.name, t2.codeName
 FROM dbo.Table1 t1, dbo.Table2 t2
 WHERE (t1.codeId = t2.id OR t1.codeId = 0)

Expected result:

Bob, 1
John, 2
Chris, 0

Upvotes: 0

Views: 68

Answers (4)

Parinda Amin
Parinda Amin

Reputation: 24

You have to use left outer join. please find below query Select codeid,name FROM Table1 LEFT OUTER JOIN Table2 ON Table1.codeId=Table2.id;

Upvotes: 0

donstack
donstack

Reputation: 2715

You are not required to use Join at all for such condition.

You can use subquery as following, it return same result as your expectation

select name,codeid from table1 where codeid in (select id from table2) 
or codeid=0

Upvotes: 1

flyby
flyby

Reputation: 136

You can use a left join. Use it to select where there is a code match in Table2 or the code_id is 0.

create table Table1
(
    name nvarchar(50),
    codeId int
)
create table Table2
(
    id int,
    codeName nvarchar(50)
)

insert into Table1
VALUES
    ('Bob', 1),
    ('John', 2),
    ('Chris', 0),
    ('Tom', -1)
-- This should be excluded .. since -1 code doesn't exist in Table2

insert into Table2
VALUES
    (1, 'Engineer'),
    (2, 'Doctor'),
    (3, 'Dentist'),
    (4, 'Pilot'),
    (5, 'Mechanic')


SELECT t1.name, t1.codeId
FROM dbo.Table1 t1
    LEFT JOIN dbo.Table2 t2 ON t1.codeId = t2.id
WHERE t2.id is not NULL or t1.codeId = 0

Upvotes: 0

Quentin Claudet
Quentin Claudet

Reputation: 43

What if you do it in two separates queries ?

Looking at the outcome, the problem must come from the WHERE clause. The OR seem to always be triggered.

So maybe splitting could do it

SELECT t1.name, t2.codeName
  FROM dbo.Table1 t1, dbo.Table2 t2
 WHERE (t1.codeId = t2.id)

SELECT t1.name, t2.codeName
  FROM dbo.Table1 t1, dbo.Table2 t2
  WHERE (t1.codeId = 0)

Upvotes: 0

Related Questions