| |

VerySource

 Forgot password?
 Register
Search
View: 1043|Reply: 8

Everyone help me see how to write this SQL

[Copy link]

1

Threads

3

Posts

4.00

Credits

Newbie

Rank: 1

Credits
4.00

 China

Post time: 2020-3-12 19:00:01
| Show all posts |Read mode
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.
Reply

Use magic Report

0

Threads

114

Posts

69.00

Credits

Newbie

Rank: 1

Credits
69.00

 China

Post time: 2020-6-7 00:45:02
| Show all posts
select * from brand table where id not in (select id from product table)
Reply

Use magic Report

0

Threads

114

Posts

69.00

Credits

Newbie

Rank: 1

Credits
69.00

 China

Post time: 2020-6-7 02:00:01
| Show all posts
select * from brand table where id not in (select brand id as id from product table)
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-6-7 11:30:02
| Show all posts
select * from brand table where id not in (select distinct id from product table)
Reply

Use magic Report

0

Threads

11

Posts

9.00

Credits

Newbie

Rank: 1

Credits
9.00

 China

Post time: 2020-6-7 12:30:01
| Show all posts
select id,title from brand table where id not in select(brand id from product registration)
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-6-7 18:30:01
| Show all posts
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)
Reply

Use magic Report

1

Threads

3

Posts

4.00

Credits

Newbie

Rank: 1

Credits
4.00

 China

 Author| Post time: 2020-6-7 20:30:01
| Show all posts
Thank you, I try
Reply

Use magic Report

0

Threads

14

Posts

12.00

Credits

Newbie

Rank: 1

Credits
12.00

 China

Post time: 2020-6-8 15:30:01
| Show all posts
select * from brand table where id not in (select brand id from product table)
Reply

Use magic Report

1

Threads

3

Posts

4.00

Credits

Newbie

Rank: 1

Credits
4.00

 China

 Author| Post time: 2020-6-9 11:15:01
| Show all posts
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.
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list