SQL

Jonax · November 20th, 2003, 09:33 PM

Erin:
Judging from your first two contributions, it seems to me that what you want are rows where Y, Z & Q are the same?

As in:
SELECT * FROM yourtable T1 WHERE EXISTS (SELECT * FROM yourtable T2 WHERE T1.Y = T2.Y AND T1.Z = T2.Z AND T1.Q = T2.Q AND T1.Num <> T2.Num)

Jeff Mason · November 21st, 2003, 08:49 AM

Now I'm completely confused.

Let's start over. erin, please post exactly what you are trying to accomplish. You must be a programmer type, otherwise why would you be here? You therefore must know that it is hard to develop software when the requirements are ill defined or keep changing.

First you said you wanted rows where there were not duplicates in columns Num, T and P. Then you say you want to check for duplicate values in the X,T and P columns only. These are radically different requirements - one wants rows where there are duplicates, and one wants them where there are not.

Then another poster comes along an interprets this to mean rows where Y, Z & Q are the same. This is a reasonable interpretation of your requirements as well.

So I don't know what you want, and can't help unless you get a bit more precise.

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

erin · November 21st, 2003, 02:15 PM

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
Ok, I just want to look for a query checks for duplicate values in the X,T and P columns only. If there is any duplicate values in these columns, I would like to get only one of the each duplicate records.

As a result, I would like to see the following table:
Num X Y Z Q T P
1 A 1 2 4 rm FW45
2 A 2 3 4 rm FW43
4 B 2 3 4 rmx FW43
6 B 3 4 5 rm FW44

Thanks for your patient...

Jeff Mason · November 21st, 2003, 03:48 PM

But wait. If there are duplicates in these columns, which row do you want returned (don't say "the first" ;) ) (there is no concept of 'first' in SQL)?

You are not consistent. I'll use Num to identify the rows. Row 1 and Row 3 have duplicate values in columns X, T, and P. You want Row 1 returned. Fine. Row 2 has no duplicates, so you want it returned. Row 3 has Row 1 as a duplicate so it should be suppressed. Row 4 has no duplicate so it should be returned.

But, row 5 and row 6 are dups but under what rule would you return row 6 instead of row 5, especially given that you wanted row 1 instead of row 3? To be consistent, I think your results should include rows 1,2,4,and 5 (not 6)

If I assume that, then the query would be:

Code:

SELECT Num, X, Q, T, P FROM yourtable T1
    WHERE (SELECT COUNT(*) FROM yourtable T2
            WHERE T1.T=T2.T AND T1.P=T2.P AND T1.X=T2.X AND T1.Num<>T2.Num)=0
    OR NOT EXISTS(SELECT * FROM yourtable T3
            WHERE T1.T=T3.T AND T1.P=T3.P AND T1.X=T3.X AND T1.Num>T3.Num)

The way this works is that the first part of the WHERE clause selects rows which have no duplicates (count=0), and the second part of the OR clause selects rows which have duplicates, but whose Num value is lower than any duplicate.

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

erin · November 21st, 2003, 04:48 PM

Sorry it doesn't work...

Let me try again. I have a table(only one table) that I am trying to eliminate some "duplicate" records from. There are 3(X,T,P) especially significant columns, and 4 (Num,Y,Z,Q)other less significant columns. I have cases where multiple records contain duplicate data in the significant columns, but not in all others. I would like to eliminate all but one record in every case of duplication in the significant columns.

Hope it helps...

Jeff Mason · November 21st, 2003, 05:28 PM

I tried that query I last posted with the exact data you supplied, and it returned the exact data you wanted it to return, except, of course, for the issue about row 5 instead of row 6.

When you say it doesn't "work", what do you mean?

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

erin · November 21st, 2003, 05:35 PM

I don't get any data from database.

Jeff Mason · November 21st, 2003, 05:40 PM

I'm pretty sure the query is correct. I set up a test table in SQL server using your data and ran the query in query analyzer and got the results I indicated.

What database are you using and how are you executing the query?

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

erin · November 21st, 2003, 05:57 PM

I created input form in asp page and I am storing the entries in the access database and displaying the data on the web.

I don't want to display any duplicate record if exists in the X,T and P columns.

I use 'distinct clause' but it eliminates all the duplicate records in the database. So for no luck
Thanks...

jemacc · November 21st, 2003, 06:45 PM

You are definitely a good person. Keep it up. Since I joined you have open my eyes.