Hi There

I have the following query:

SELECTdistinct
cast(RT_VALUATION.RT_VAL_ID1 as varchar) RT_VAL_ID1,
cast(RT_VALUATION.RT_VAL_ID2 as varchar) RT_VAL_ID2 ,
cast(RT_VALUATION.RT_VAL_ID3 as varchar) RT_VAL_ID3,
cast(RT_VALUATION.RT_VAL_ID4 as varchar) RT_VAL_ID4,
cast(RT_VALUATION.RT_VAL_LOCATION as varchar) RT_VAL_LOCATION,
cast(RT_VALUATION__RT_VAL_OWNERS.RT_VAL_OWNERS as varchar) RT_VAL_OWNERS,
cast(RT_VALUATION__RT_VAL_OCCUPIERS.RT_VAL_OCCUPIERS as varchar)as RT_VAL_OCCUPIERS
FROM RT_VALUATION INNERJOIN
RT_VALUATION__RT_VAL_OWNERS ON RT_VALUATION.RT_VALUATION__ID = RT_VALUATION__RT_VAL_OWNERS.RT_VALUATION__ID INNERJOIN
RT_VALUATION__RT_VAL_OCCUPIERS ON RT_VALUATION.RT_VALUATION__ID = RT_VALUATION__RT_VAL_OCCUPIERS.RT_VALUATION__ID
WHERE RT_VALUATION__RT_VAL_OWNERS.RT_VAL_OWNERS notlike RT_VALUATION__RT_VAL_OCCUPIERS.RT_VAL_OCCUPIERS
ORDERBY RT_VAL_ID1,RT_VAL_ID2,RT_VAL_ID3,RT_VAL_ID4

and it gives me the results as this:

03710 001 00 NULL 168 BOILER GULLY RD 127715 129805
03710 001 00 NULL 168 BOILER GULLY RD 129805 127715
03710 002 00 NULL 140 WATTLE BAY RD 171438 710
03710 002 00 NULL 140 WATTLE BAY RD 710 171438
03710 002 02 NULL 196 WATTLE BAY RD 104992 152463
03710 002 02 NULL 196 WATTLE BAY RD 152463 104992

you will notice that it gives me two lines where I only need one eg:

I would only like to have returned one row: eg:
03710 001 00 NULL 168 BOILER GULLY RD 127715 129805
Any help woul be much appreciated

Thanks

Greg

Could it possibly be a data problem? You've got the same people as owners and occupiers at 168 Boiler Bully Rd. In once case they are the owner and the other occupier. Wouldn't that row read:

03710 001 00 NULL 168 Boiler Gully Rd. 127715 127715

or

03710 001 00 NULL 168 Boiler Gully Rd. 129805 129805

This would have them as owner and occupier.

Hi, This is correct. I am trying to extract where the owner and the occupier are different and enup with more than one row and I only want to end up with one line - it could be a data problem - not sure

DISTINCT results in unique rows, so the SELECT list should be taken care accordingly.

__________________
- Vijay G

You are pulling unique rows in your result set. It's just that you have two owners for the same property. It seems that there could only be one owner of a property at a time.