New to mysql for multi-table query, don't know how to use the
I want to find a name="name" in table 1 conditions and there is no this ID in the table 2 personnel ID and name, how to use a select statement to get information
Table 2 table 1

ID name ID sex
1 a: xiao li men
2 wang 3 female


As the table above, I want is that the name of the table 1 for wang ID and don't save this ID information in the table 2 ID that is to say, I want 2 name wang

If with two, should be the first to find table 1 wang the ID of the
Select the ID from table 1 where name='wang'
Then
Select table 1 get ID from table 2 where ID='table 1 to get the ID of the



Expression is not too clear, forgive me

CodePudding user response:

Exist IN, does NOT exist with the NOT IN

 select ID from table 1 where name='wang' where ID not in (select ID from table 2.) 

CodePudding user response:

Or use the EXISTS, NOT the EXISTS

 select ID from table 1 where name='wang' 
WHERE the EXISTS (SELECT * from table 2 WHERE ID=table 1. ID 

CodePudding user response:

Error, using AND, instead of two WHERE

 select ID from table 1 where name='wang' AND ID not in (select ID from table 2.) 
Select the ID from table 1 where name='wang' AND the EXISTS (select * from table 2 where ID=table 1. ID 

CodePudding user response:

Select id, name from table 1 a left join table 2 b on Anderson, d=b.i d where b.i d is null
The original poster can try, should be able to solve the problem

CodePudding user response:

The building Lord knot posted

CodePudding user response:

This demand, because involves two table data, so the link can be used in processing:

Select distinct t1. * from table 1 t1
Left the join table 2 t2
On t1. Id=t2. Id
Where a t1. Name='wang'
And t2. The name is a null

Subquery can also be used to implement, such as 1 to 2 floor of writing

CodePudding user response:

"Stickers for the boy

CodePudding user response:

And stick to the point, the boy

refer to the second floor ZJCXC response:

or use the EXISTS, NOT the EXISTS

 select ID from table 1 where name='wang' 
WHERE the EXISTS (SELECT * from table 2 WHERE ID=table 1. ID)