select particular data from table1 which is not in table2 with where clause
I want to select data from table1 which is not in table2 but i have to select a particular data from table1
my data tables
CREATE TABLE IF NOT EXISTS `table3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`acc_id` int(11) NOT NULL DEFAULT '0',
`did` int(11) NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE IF NOT EXISTS `table2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`acc_id` int(11) NOT NULL,
`table1_id` int(11) NOT NULL,
`did` int(11) NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE IF NOT EXISTS `table1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`acc_id` int(11) NOT NULL DEFAULT '0',
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
)
I want to do
select name,id from table1 where id !=(select table1_id from table2 join table3 on table2.acc_id=table3.acc_id where table2.did=4759505 and table2.acc_id=2)and table1.acc_id=2
This above query woks fine if subquery returns 1 row but not if subquery returns multiple row
Thanks
You can just change the != to not in :
select name, id
from table1
where id not in (select table1_id
from table2 join
table3
on table2.acc_id = table3.acc_id
where table2.did = 4759505 and table2.acc_id = 2
) and
table1.acc_id = 2;
Note: you should also be sure that table1_id in the subquery is never NULL . NOT IN can be non-intuitive in this case. Often, I prefer NOT EXISTS :
select name, id
from table1
where not exists (select table1_id
from table2 join
table3
on table2.acc_id = table3.acc_id
where table2.did = 4759505 and table2.acc_id = 2 and
table1_id = table1.id
) and
table1.acc_id = 2;
This handles the NULL value more intuitively.
上一篇: MySQL选择左连接为空的行