>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
