asked on

Simple SQL join question. (I think I need a self join here with a distinct or maybe group by.)

Take this table – Columns ‘Key’, ‘Name’, ‘Street’ and ‘ZIP
123      Smith      Main St            80424
123      Smith      Main St            80424
234      Jones      Main St            80424
345      Clark      Main St            80424

Input information for where clause is street name only. I want to return three records (123, 234,345). So, just one occurrence of 123 (either one).
Select distinct Key, Name, Street, ZIP from table where street = ‘Main St’
Returns nothing

knightEknight

Your query should return something... try trimming the spaces off of the Streen name:

select distinct Key, Name, Street, ZIP from table where LTRIM(RTRIM(Street)) = 'Main St'

sabev

ASKER

It returns all the data if I leave off the distinct so it's not a problem with spaces.

I simplified the SQL code in my example (for instance, the street name is made up of multiple fields). The actual code follows and only returns data if I leave off the distinct.

ActionSQLCMD.CommandText = "select street_house, schno, ppi, street_pdir, street_name, street_suff, street_sdir, street_no from at_loc where street_pdir= '" & strPdir & "' and street_name = '" & strStreet & "' and street_suff = '" & strSuff & "' and street_sdir = '" & strSdir & "' and street_no = '" & strCRNO & "'and street_town = '" & strTown & "' and action = 'C' and schno < '9000000'"

In this real code, the column that is represented by 'KEY' in my original example is PPI

Thanks

Anthony Perkins

It sounds like you need to test your "real code" in SSMS and let us know that is the outcome.

sabev

ASKER

More info - I have an order by statement at the end that uses the cast function. That's making it fail if I have the distinct key word. Either distinct or cast works fine on it's own.
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Yet, street_house is in the select list.

select distinct ppi, street_house, schno, street_pdir, street_name, street_suff, street_sdir, street_no from at_loc where
street_pdir= 'N' and street_name = 'FULLER PLACER' and street_suff = 'RD' and street_no = '560' and schno < '9000000'
ORDER BY cast(street_house AS VARCHAR(MAX))

However, when it does work, I do not get the results I would expect. I'm looking for the distinct PPI. No repeats. I'm getting duplicates. I suspect that distinct is operating on all the fields and not just ppi. Schno makes each row 'distinct'. I suspect that distinct is not the answer I'm looking for.

ppi      street_house      schno      street_pdir      street_name      street_suff      street_sdir      street_no      action
2371-0540-01-008      0050      1800496      N      FULLER PLACER       RD             560      C
2371-0540-01-007      0052      1800495      N      FULLER PLACER       RD             560      C
2371-0540-04-007      0057      1800563      N      FULLER PLACER       RD             560      C
2371-0540-04-007      0057      1800564      N      FULLER PLACER       RD             560      C
2371-0540-04-007      0057      1800565      N      FULLER PLACER       RD             560      C

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sabev

ASKER

Sorry, I got called out of town. I'll try this ASAP and get back to you.

sabev

ASKER

That did it. Thanks