Table An is associated with Table B, querying data that is present in Table A but not in Table B.

Table A

< table > < thead > < tr > < th align= "left" > id < / th > < th align= "left" > column1 < / th > < th align= "left" > column2 < / th > < / tr > < / thead > < tbody > < tr > < td align= "left" > 1 < / td > < td align= "left" > a < / td > < td align= "left" > A1 < / td > < / tr > < tr > < td align= "left" > 2 < / td > < td align= "left" > a < / td > < td align= "left" > a2 < / td > < / tr > < tr > < td align= "left" > 3 < / td > < td align= "left" > a < / td > < td align= "left" > A3 < / td > < / tr > < tr > < td align= "left" > 4 < / td > < td align= "left" > b < / td > < td align= "left" > b1 < / td > < / tr > < tr > < td align= "left" > 5 < / td > < td align= "left" > b < / td > < td align= "left" > b2 < / td > < / tr > < tr > < td align= "left" > 6 < / td > < td align= "left" > b < / td > < td align= "left" > b3 < / td > < / tr > < tr > < td align= "left" > 7 < / td > < td align= "left" > c < / td > < td align= "left" > C1 < / td > < / tr > < / tbody > < / table >

Table B

< table > < thead > < tr > < th align= "left" > id < / th > < th align= "left" > column1 < / th > < th align= "left" > column3 < / th > < / tr > < / thead > < tbody > < tr > < td align= "left" > 1 < / td > < td align= "left" > a < / td > < td align= "left" > A1 < / td > < / tr > < tr > < td align= "left" > 2 < / td > < td align= "left" > a < / td > < td align= "left" > a2 < / td > < / tr > < tr > < td align= "left" > 3 < / td > < td align= "left" > b < / td > < td align= "left" > b1 < / td > < / tr > < tr > < td align= "left" > 4 < / td > < td align= "left" > b < / td > < td align= "left" > b2 < / td > < / tr > < / tbody > < / table >

Table An and Table B are associated through the column1 field. The filter condition is that the value of column2 in Table A does not have a corresponding value in Table B"s column3 . According to the data in the table above, the result should be:

< table > < thead > < tr > < th align= "left" > id < / th > < th align= "left" > column1 < / th > < th align= "left" > column2 < / th > < / tr > < / thead > < tbody > < tr > < td align= "left" > 3 < / td > < td align= "left" > a < / td > < td align= "left" > A3 < / td > < / tr > < tr > < td align= "left" > 6 < / td > < td align= "left" > b < / td > < td align= "left" > b3 < / td > < / tr > < tr > < td align= "left" > 7 < / td > < td align= "left" > c < / td > < td align= "left" > C1 < / td > < / tr > < / tbody > < / table >
Dec.24,2021

select * from a where not exists 
(select 1 from b where a.column1=b.column1 and a.column2=b.column2;

I don't understand. Why isn't id=4 the data you want?


select * from tableA where id not in (select a.id from tableA a,tableB b where a.column1=b.column1 and a.column2=b.column3)
< hr >
select * from(
select * from tableA
union all
select a.* from tableA a,tableB b where a.column1=b.column1 and a.column2=b.column3
)temp group by id having count(id)=1

both of the above methods can be used

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b32062-4ea45.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b32062-4ea45.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?