I'm sorry for missing the key word "not", it should be SELECT * FROM approvals WHERE adminname != 'admin2'and photo_uid not in (select photo_uid from approvals where adminname='admin2'),
在2009-11-22，shjunsin <shjun...@163.com> 写道： >Hi\I think you can achieve this using a single query like this: SELECT * FROM >approvals WHERE adminname != 'admin2'and photo_uid in (select photo_uid from >approvals where adminname='admin2'), of course, maybe it's not the best >solution, just for your information.在2009-11-22，"Ashley M. Kirchner" ><kira...@gmail.com> 写道： >>I'm stuck again ... and perhaps it's just not designed right, but I >>inherited this setup, so if anyone has suggestions on how to make it better, >>I'm all ears. This is all one table ... >> >>describe approvals; >>+-----------+--------------------------+------+-----+---------+----------------+ >>| Field | Type | Null | Key | Default | >>Extra | >>+-----------+--------------------------+------+-----+---------+----------------+ >>| id | int(6) | NO | PRI | NULL | >>auto_increment | >>| photo_uid | int(7) unsigned zerofill | NO | | NULL >>| | >>| adminname | varchar(100) | NO | | NULL >>| | >>| status | int(1) | NO | | NULL >>| | >>+-----------+--------------------------+------+-----+---------+----------------+ >> >> >>SELECT * FROM approvals; >>+----+-----------+-----------+--------+ >>| id | photo_uid | adminname | status | >>+----+-----------+-----------+--------+ >>| 6 | 0000028 | admin1 | 1 | >>| 13 | 0000016 | admin1 | 0 | >>| 49 | 0000016 | admin2 | 1 | >>| 16 | 0000018 | admin1 | 1 | >>| 50 | 0000018 | admin2 | 0 | >>+----+-----------+-----------+--------+ >> >> >>The goal, here is to capture everything that does not have 'admin2' in the >>adminname column, however duplicated records in the photo_uid column should >>also be excluded. >> >>SELECT * FROM approvals WHERE adminname != 'admin2'; >>+----+-----------+-----------+--------+ >>| id | photo_uid | adminname | status | >>+----+-----------+-----------+--------+ >>| 6 | 0000028 | admin1 | 1 | >>| 13 | 0000016 | admin1 | 0 | >>| 16 | 0000018 | admin1 | 1 | >>+----+-----------+-----------+--------+ >> >> >>However, I also need to exclude photo_uid '0000016' and '0000018' because >>both of them are already tagged by 'admin2'. The only record that needs to >>be returned here is '0000028' ... >> >>I just don't know how to do that, or if it's even possible to do in a single >>query. I can get it done in two, but I'm hoping for a single query here ...
