mysql - How to join results of 2 tables based on not condition -

i have 2 tables, tablea & tableb defined this:

create table tablea(id int, name varchar(20), partnumber varchar(30));

with values as:

insert tablea values(1,'a1','10'); insert tablea values(2,'a2','20'); insert tablea values(3,'a3','30'); insert tablea values(4,'a4','40'); 

create table tableb(id int, name varchar(20), partnumber varchar(30));

insert tableb values(5,'b1','10'); insert tableb values(6,'b2','20'); insert tableb values(7,'b3','60'); insert tableb values(8,'b4','70'); 

now in result want join both tables , records tableb , records tablea tablea's partnumber column value not matching value of tableb's partnumber.

i have tried below queries , of them gave same result of 14 records not correct.

query 1 :

select b.id bid, b.name bname, b.partnumber bpart, a.id aid, a.name aname, a.partnumber apart tableb b left join tablea on a.partnumber!=b.partnumber;

query 2 :

select b.id bid, b.name bname, b.partnumber bpart, a.id aid, a.name aname, a.partnumber apart tableb b , tablea a.partnumber!=b.partnumber;

query 3 :

select b.id bid, b.name bname, b.partnumber bpart, a.id aid, a.name aname, a.partnumber apart tableb b left join tablea on a.partnumber not in (select a.id aid tableb b join tablea on a.partnumber=b.partnumber);

can please me making mistake here? correct way results.

i expecting output this:

+------+-------+-------+------+-------+-------+ | bid  | bname | bpart | aid  | aname | apart | +------+-------+-------+------+-------+-------+ |    5 | b1    | 10    |    3 | a3    | 30    | |    5 | b1    | 10    |    4 | a4    | 40    | |    6 | b2    | 20    |    3 | a3    | 30    | |    6 | b2    | 20    |    4 | a4    | 40    | |    7 | b3    | 60    |    3 | a3    | 30    | |    7 | b3    | 60    |    4 | a4    | 40    | |    8 | b4    | 70    |    3 | a3    | 30    | |    8 | b4    | 70    |    4 | a4    | 40    | +------+-------+-------+------+-------+-------+ 

so here mean in result don't want records of tablea partnumber 10, 20 because values present in tableb's partnumber.

you can use following query:

select id, name, partnumber, aid, aname, apart tableb t cross join (select id aid, name aname, partnumber apart             tablea             not exists (select 1                               tableb b                               b.partnumber = a.partnumber)) c order id     

the idea select required records tablea using not exists clause. cross join derived table query table1 possible combinations.

demo here