Re: Basic SQL

John Gilson wrote: 
"Stefan Rybacki" <stefan.rybacki@xxxxxxx> wrote in message news:3jlhr8Fqd4cmU2@xxxxxxxxxxxxxxxxx

John Gilson wrote:

"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



Ui two subselects? :/ I even don't like the one I used in my solution ;)

Regards
Stefan



Given the original poster's stated input and required output, your query doesn't
do the trick.

--
JAG


Ok you're right, didn't recognize that the op wanted to get distinct values. But one subselect is still enough

SELECT DISTINCT ID,VALUE FROM table2 T2 WHERE EXISTS (SELECT * FROM table2 WHERE ID=t2.ID AND VALUE<>T2.VALUE)

Regards
Stefan
.

Relevant Pages

  • Re: Basic SQL
    ... John Gilson wrote: ... have multiple values for column b, but cannot figure it out - can someone help? ... The results of the query when running against the above data ... Regards ...
    (comp.databases)
  • Re: Multiuser record lock query?
    ... Here I have multiple tables, and I need a single unique sequential number (It's a docket number) each time a record is added to any of the tables. ... Thankyou for taking the time to answer my query. ... Regards ...
    (microsoft.public.data.ado)
  • multiple table results as one table in dataset
    ... I have a problem in returning data from multiple table as one table into ... as we know that in dataset we can populate it with multiple tables, ... i want the result of a following query which is coming from multiple ...
    (microsoft.public.vb.database.ado)
  • Re: SQL query help
    ... the multiple where criterion. ... I downloaded a query onto a table for a test ... numeric datatypes in Access do not have precision and scale the way ...
    (microsoft.public.access.queries)
  • RE: Loops
    ... table with multiple columns 5 for each pay period, ... I run my query for the pay period ending on 01/05/07 ... you are updating multiple tables. ...
    (microsoft.public.access.modulesdaovba)