Compare 2 Table ID and return with a Boolean-CodePudding

Query for SQL-Server:

SELECT table1.id, talbe2.id,
       IIF(talbe2.id IS NULL, 'FALSE', 'TRUE') as bolean
FROM table1
LEFT JOIN talbe2 ON (table1.id = talbe2.id);

SELECT * FROM table1

SELECT * FROM talbe2

Query Output for SQL-Server:

Our query in MySQL is:

SELECT table_1.table1_id, table_2.table2_id,
       IF(table_2.table2_id IS NULL, FALSE, TRUE) as bolean
FROM table_1
LEFT JOIN table_2 ON (table_1.table1_id = table_2.table2_id);

Table 1 From MySQL:

Table 2 From MySQL:

Output From MySQL: The output was right and did my friend goal however this is MySQL and I have no background knowledge in SQL Server since Im also just a student. Our goal here is to get this output but on SQL Server implementation. However the SQL-Server query only showing TRUE and now showing the FALSE we also want it to show.

CodePudding user response：

You can implement using below

example:

drop table if exists #temp;

select 1 as table1_id ,'A' as table1_Name 
into #temp union all
select 2,'B' union all
select 3,'A' union all
select 4,'B' union all
select 5,'A' 
 
drop table if exists #temp1;  

select 1 as table2_id ,'A' as table1_Name 
into #temp1   union all
select 2,'B'union all
select 3,'A'union all
select 4,'B'

SELECT table_1.table1_id, table_2.table2_id,
case when  table_2.table2_id IS NULL then 0
else 1 end as [boolean]
FROM #temp table_1
LEFT JOIN #temp1 table_2 ON (table_1.table1_id = table_2.table2_id);

or 


SELECT table_1.table1_id, table_2.table2_id,
iif(table_2.table2_id IS NULL,0,1) as [boolean]
FROM #temp table_1
LEFT JOIN #temp1 table_2 ON (table_1.table1_id = table_2.table2_id);

CodePudding user response：

Query:

/*Table create*/
CREATE TABLE table1 (
    id INT PRIMARY KEY IDENTITY (1, 1),
    info VARCHAR (50) NOT NULL,
);

CREATE TABLE table2 (
    id INT PRIMARY KEY IDENTITY (1, 1),
    info VARCHAR (50) NOT NULL,
);

/*Data*/
INSERT INTO table1
(info)
VALUES
('test1'),
('test2'),
('test3'),
('test4');

INSERT INTO table2
(info)
VALUES
('test1'),
('test2'),
('test3'),
('test4'),
('test5'),
('test6');

SELECT * FROM table1;

SELECT * FROM table2;

/*View*/
SELECT table1.id, table2.id,
IIF(table2.id IS NULL, 'FALSE', 'TRUE') as bolean
FROM table1
LEFT JOIN table2 ON (table1.id = table2.id);

Output: