SQL query returning all rows from Table2-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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)

CodePudding user response：

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

CodePudding user response：

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;