Re: Basic SQL

"MS" <mesemailoffers@xxxxxxxxxxx> wrote in message
news:1121280375.735695.277470@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> I am trying to write a sql statement that gives me the records that
> have multiple values for column b, but cannot figure it out - can
> someone help?
>
> My table is as follows:
>
> ID Value
> 1234 S
> 1234 S
> 1234 X
> 4444 S
> 5555 X
> 5555 Y
>
> I want to see what records have two distinct values for the "Value"
> column. The results of the query when running against the above data
> should be as follows:
>
> ID Value
> 1234 S
> 1234 X
> 5555 Y
> 5555 X
>
> Any help would be appreciated.
>
> Thanks!

SELECT ID, Value
FROM (SELECT DISTINCT id, value FROM T) AS T
WHERE EXISTS (SELECT * FROM T AS T1
WHERE T1.ID = T.ID AND
T1.Value <> T.Value)

--
JAG


.

Relevant Pages

  • Basic SQL
    ... I am trying to write a sql statement that gives me the records that ... have multiple values for column b, but cannot figure it out - can ... The results of the query when running against the above data ... Prev by Date: ...
    (comp.databases)
  • Re: Basic SQL
    ... >>>I am trying to write a sql statement that gives me the records that ... >> JAG ... Given the original poster's stated input and required output, your query doesn't ... Prev by Date: ...
    (comp.databases)
  • Re: HELP WITH UPDATE QUERY
    ... each selected items in the list, and, in the example, make an SQL statement ... the user wants to select MULTIPLE items from this list ... query named qrProd and the list box is from that same query, ... I ASSUMED the control name was. ...
    (microsoft.public.access.queries)
  • Re: Idea of SQL integration
    ... this issues like compiler directive e.g. ASM. ... is no general solution for multiple DB vendors (sas for multiple ... It gets a SQL statement and another parameter: Open, Execute, ExecuteAndFree. ...
    (borland.public.delphi.non-technical)
  • Re: Conditional test to combine multiple records
    ... >> current sql statement. ... (or just switch to the query grid and do the ... >>> one record becaue I count the total number of shipped orders in another ... >>> over the course of multiple dates. ...
    (microsoft.public.access.queries)