SQL

erin · November 20th, 2003, 04:24 PM

Hi All,

I am trying to create sql code that I would like to get the row Num 2 and row Num4. In other words, I am trying to pull the records that shoud not duplicate in column Num, T and P.

Num X Y Z Q T P
1 A 1 2 4 rm FW45
2 A 2 3 4 rm FW43
3 A 6 7 8 rm FW45
4 B 2 3 4 rmx FW43
5 B 1 2 3 rm FW44
6 B 3 4 5 rm FW44

Thanks...

Jeff Mason · November 20th, 2003, 05:44 PM

Not sure I understand what you are looking for for output, but how about:

Code:

SELECT Num FROM yourtable T1
    WHERE NOT EXISTS(SELECT * FROM yourtable T2
        WHERE T1.T=T2.T AND T1.P=T2.P AND T1.Num<>T2.Num);

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

erin · November 20th, 2003, 05:54 PM

I am trying to obtain the following table. And I have only one table.

Num X Y Z Q T P
2 A 2 3 4 rm FW43
4 B 2 3 4 rmx FW43

Thanks...

Jeff Mason · November 20th, 2003, 06:01 PM

OK. Just add the additional columns to the SELECT list - this query checks for duplicate values in the T and P columns only. (And Num, but that is always unique as far as I can see).

Code:

SELECT Num, X, Y, Z, Q, T, P FROM yourtable T1
    WHERE NOT EXISTS(SELECT * FROM yourtable T2
        WHERE T1.T=T2.T AND T1.P=T2.P AND T1.Num<>T2.Num);

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

erin · November 20th, 2003, 06:16 PM

I am trying to check for duplicate values in the X,T and P columns only. I don't have second table that I can compare these values, I have only one table.

Thanks...

Jeff Mason · November 20th, 2003, 06:22 PM

Quote:

quote:Originally posted by erin
I am trying to check for duplicate values in the X,T and P columns only. I don't have second table that I can compare these values, I have only one table.

...and the query I posted should do that for you...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

erin · November 20th, 2003, 06:29 PM

it doesn't work. I can't return any data from database...

Thanks

Jeff Mason · November 20th, 2003, 06:44 PM

I don't know what to say.

I set up a table with exactly the data you supplied and ran the query I posted, and it returned the correct results.

Perhaps if you posted the query you tried (along with the exact structure of your table)?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff Mason · November 20th, 2003, 06:47 PM

Wait.

I just noticed.

In your original post you said:

Quote:

quote:
I am trying to pull the records that shoud not duplicate in column Num, T and P.

And in a later post you said:

Quote:

quote:
I am trying to check for duplicate values in the X,T and P columns only

So which is it?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

erin · November 20th, 2003, 06:55 PM

Yes, you are right, second quote is true which is
-I am trying to check for duplicate values in the X,T and P columns only.