Re: SQL syntax I've been unable to figure out....

2008/12/25 Karl Denninger <karl(at)denninger(dot)net>:
> Assuming a table containing:
>
> name text
> address text
> uri text
>
> I wish to run a query that will return those rows where:
>
> ("name" is not null) AND (distinct) (uri is the same for two or more entries
> AND name is different between the two entries))
>
> Example data:
>
> george who 1
> sam where 2
> sam what 2
> [null] why 2
> leroy never 2
>
> Returns:
>
> sam 2
> leroy 2

CREATE TABLE TT
(NAME TEXT, ADDRESS TEXT, URI TEXT) ;

INSERT
INTO TT VALUES
('george', 'who', 1)
, ('sam', 'where', 2)
, ('sam', 'what', 2)
, (NULL, 'why', 2)
, ('leroy', 'never', 2) ;

SELECT TT.NAME, TT.URI
FROM
(SELECT URI FROM TT WHERE NAME IS NOT NULL GROUP BY URI HAVING SUM(1) > 1
) A
JOIN TT ON A.URI = TT.URI AND TT.NAME IS NOT NULL
GROUP BY TT.NAME, TT.URI;