0 
SELECT P.CODE, P.STATUS, G.DESCRIPTION, x.xref_code, X.XREF_STATUS FROM table1.price_data p, TABLE2.GENERAL_DATA G, TABLE3.XREF_DATA X WHERE P.CODE = G.CODE AND G.CODE=X.CODE AND x.code=4545645 AND X.VEHICLE_CODE=9999999

There are literally 30,000 rows to filter through...how do i alter the above query to show only unique values of x.xref_status?

Improve this question

1 Answer 1

Reset to default
1

It's not clear why your X.XREF_STATUS duplicates: there are dups in TABLE3 table? Or, more likely, there are many references to XREF_STATUS values in related records?

If rows differs for other columns than X.XREF_STATUS, you won't be able to pick one row instead of another to show only unique X.XREF_STATUS values.

A list of unique X.XREF_STATUS values can be obtained with:

select distinct X.XREF_STATUS from TABLE3.XREF_DATA X where x.code=4545645 AND X.VEHICLE_CODE=9999999

This can be joined to other tables to obtain related data, but again if you join with a table containing more than one reference to X.XREF_STATUS this will dup accordingly. You must aggregate other columns (COUNT, SUM, etc.) to avoid dups.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

yes there are both dups in table3 table, and also references to xref_status values in related tables too in the query
The distinct query solves dups in table3. Multiple reference are hareder to remove because they're a more application specific issue; you can use distinct here too and try to remove from select the columns that cause the entire record to be unique.
youre right, multiple references are a pain...thanks Andrea, it worked for me!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.