Easier and pretty obvious :) Thanks Keith
Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 29 June 2018 at 23:20, Keith Medcalf <kmedc...@dessus.com> wrote: > >I want a query that returns all of the records with status = 1 and > >unique records, based on name, where the status =0 and the name is > >not in the list status=1 > > Translation into SQL using English to SQL Translator, using the most > direct translation on the "problem statement" above directly into SQL: > > create table names (id int, status int, name text); > insert into names values (1, 1, 'paul'); > insert into names values (2, 1, 'helen'); > insert into names values (3, 0, 'steve'); > insert into names values (4, 0, 'steve'); > insert into names values (5, 0, 'pete'); > insert into names values (6, 0, 'paul'); > > -- I want a query that returns all of the records with status = 1 > > SELECT id, > status, > name > FROM names > WHERE status == 1 > > -- and > > UNION > > -- unique records, based on name, where the status = 0 and the name is not > in the list [of names where] status=1 > > SELECT id, > status, > name > FROM names > WHERE status == 0 > AND name NOT IN (SELECT name > FROM names > WHERE status == 1) > GROUP BY name; > > Returns the rows: > > 1|1|paul > 2|1|helen > 3|0|steve > 5|0|pete > > If the table is bigger than trivial (ie, contains more than the number of > rows you can count with your fingers) then you will need the appropriate > indexes to achieve performant results. > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > >-----Original Message----- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson > >Sent: Friday, 29 June, 2018 09:50 > >To: General Discussion of SQLite Database > >Subject: [sqlite] unique values from a subset of data based on two > >fields > > > >I have a table > > > >Create table names (id int, status int, name text) > > > > > > > >1, 1, 'paul' > > > >2, 1,'helen' > > > >3, 0, 'steve' > > > >4, 0, 'steve' > > > >5, 0, 'pete' > > > >6, 0, 'paul' > > > > > > > >I want a query that returns all of the records with status = 1 and > >unique > >records, based on name, where the status =0 and the name is not in > >the list > >status=1 > > > > > > > >So from the above I would want to see > > > > > > > >1, 1, paul > > > >2, 1, helen > > > >3, 0, steve (or 4, 0, steve) > > > >5, 0, pete > > > > > > > >I could do something like > > > > > > > >Select * from names where status = 1 or name not in (select name from > >names > >where status = 1) > > > > > > > >But this gets both rows for steve, e.g. > > > > > > > >1, 1, paul > > > >2, 1, helen > > > >3, 0, steve > > > >4, 0, steve > > > >5, 0, pete > > > >while I am not bothered about which of the two steves I get back, I > >must > >have all occurences of names with status = 1 > > > >I am probably missing somethng obvious > > > >Paul > >www.sandersonforensics.com > >SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> > >_______________________________________________ > >sqlite-users mailing list > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
