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

sabev
sabev used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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'

Author

Commented:
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
Top Expert 2012

Commented:
It sounds like you need to test your "real code" in SSMS and let us know that is the outcome.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
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                    

Top Expert 2012
Commented:
Something like this perhaps:
;WITH    CTE_at_loc AS (
	SELECT	ppi,
		street_house,
		schno,
		street_pdir,
		street_name,
		street_suff,
		street_sdir,
		street_no,
		ROW_NUMBER() OVER (PARTITION BY ppi ORDER BY CAST(street_house AS varchar(MAX))) Row
	FROM	at_loc
	WHERE	street_pdir = 'N'
		AND street_name = 'FULLER PLACER'
		AND street_suff = 'RD'
		AND street_no = '560'
		AND schno < '9000000'
             )
SELECT	ppi,
	street_house,
	schno,
	street_pdir,
	street_name,
	street_suff,
	street_sdir,
	street_no
FROM	CTE_at_loc
WHERE	Row = 1
ORDER BY
	CAST(street_house AS VARCHAR(MAX))

Open in new window

Author

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

Author

Commented:
That did it.  Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial