Everyone help me see how to write this SQL

jjzhangwei

Two tables, one brand table, one product table
Brand table records brand information

ID TITLE
1 Nokia
2 Motorola
3 Siemens

Product table records brand table ID
PRODUCT_ID TITLE Brand ID
1 3310 1

I want to find out the records of all brands that have not yet registered products in the product table. For example, there is no Siemens product in the product table.

我是杀人狂

select * from brand table where id not in (select id from product table)

我是杀人狂

select * from brand table where id not in (select brand id as id from product table)

fanfan1980

select * from brand table where id not in (select distinct id from product table)

啊啦是奶牛

select id,title from brand table where id not in select(brand id from product registration)

fanfan1980

create table A(ID int, TITLE nvarchar(10))
insert A select 1,'Nokia'
union all select 2,'Motorola'
union all select 3,'Siemens'

create table B(PRODUCT_ID int, TITLE varchar(10), brand ID int)
insert B select 1, '3310', 1
--1
select * from A
where ID not in
(select distinct brand ID from B)
--2
select * from A as A
where not exists(select 1 from B where brand ID=A.ID)

jjzhangwei

Thank you, I try

imapiggy

select * from brand table where id not in (select brand id from product table)

jjzhangwei

Another question is about bulk insertion. For example, how can certain qualified records in product table 1 be added to product table 2 in bulk? Some fields of product table 1 and product table 2 are the same, but not all fields are exactly the same.