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

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'

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

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

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                    

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

That did it.  Thanks