only need 5 distinct values and have 10+ fields in my select list

[aj200100]

I have a query with 3 tables ('Table A', 'Table B', and 'Table C') joined and am getting to many non distinct rows.
I want to have all the fields returned where all fields in table A are distinct and 1 distinct field in Table's B and C.
For instance I want to be able to write:

select distinct(a.field1, a.field2, a.field3) B.field_x, Distinct(b.field_y) C.field_x, C.field_y (distinct C.field_z)
from tableA
inner join TableB
inner join TableC

How would I do that? I am getting way to many rows back.
I only need 5 distinct values and have 10+ fields in my select list.
Thanks in advance.

 

[markros]

select distinct Field1, field2, field3 from A
UNION
select Field1, max(Field2), max(Field3) from B group by Field1 
UNION
select Field1, Field2, max(Field3) from C group by Field1, Field2

Something like the above.

 

[hmckillop]

Markros

Will that work, I thought the whole purpose of UNION was to join similiar queries with identical number of fields and types must be compatible

To do what Aj is asking we need to understand further exactly what is required. How he is proposing distinct doesnt make sense - have a look at

http://blog.sqlauthority.com/2007/12/20/sql-server-distinct-keyword-usage-and-common-discussion/

The concept of the three tables having distinct fields is fine, but once they are joined it doesnt always make sense.

AJ if you can give an example of the data you have in the tables, the desired result I am sure someone can come up with a solution



"I'm living so far beyond my income that we may almost be said to be living apart

 

[RyanEK]

Hi,

Perhaps something like the following?

select distinct a.field1, a.field2, a.field3, b.*, c.*
from tableA a
inner join (
 select distinct fieldx, fieldy
 from tableB) b on a.id = b.id
inner join (
 select distinct fieldx, fieldy, fieldz
 from tableC) c on a.id = c.id

Ryan

 

[markros]

You're right, I read the requirement too quickly, I thought he was talking about UNION. May be what Ryan is suggesting it what we need here or better have the create tables scripts, some input and desired output. Also knowing the SQL Server version may be helpful.

 

[SQLSister]

You can't do that with distinct. The problem you have to define is how to chose the value you want for the other fields when you have multiple records that match to the fileds you want distinct values for. Once you know that you will use group by and aggreagete functions to pick out the correct values. If the rules are very complex, you might even need derived tables for the joins. But until you define which record you want when the join returns more than one, you can't write code to do this.

"NOTHING is more important in a database than integrity." ESquared