Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Complex Join

Posted on 2004-03-24
2
309 Views
Last Modified: 2008-02-01
I will give 1500 points because the difficulty and urgency of the code. in order to get the other 1000 points I will set up 2  other question one called thanks1 and thanks2 each worth 500 point and only award it to the ONE who figures it out*****************


I need to return a search of members and return there MemberID within a distance from a Particular zipcode.
I have a Member Table which has my members Info I can pass there zipcode in and return other members within there zipcode, but I also need to get a rowcount and show that distance other members are from the 90210 zipcode
***The following code does work to return Distance, City and Zipcode from the 90210 Zipcode, but not my member info or the record count***
***Below this code is my code to return member info from the radius of a zipcode, but not  the rowcount or the actual distance ***


**The code below returns All Cities,zipcode and the distance within 25.12 miles of the 90210 zipcode***
Zipcode            City               Distance
90210         Beverly hills              0.0
90209         Beverly Hills         1.368293
90076         Los Angeles         1.9350587
ETC...



DECLARE @ZIPCode nchar(5)
SET @ZIPCode = 90210

DECLARE @Miles REAL
SET @Miles = 25.12

DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @ZIPCode AND CityType = 'D'

if @RowCount > 0
      BEGIN
            SELECT
                  z.ZIPCode, z.City,
                  dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
                  /*
                        The above functions requires the Distance Assistant.
                  */
            FROM
                  ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r
            WHERE
                  z.Latitude <= r.MaxLat
                  AND z.Latitude >= r.MinLat
                  AND z.Longitude <= r.MaxLong
                  AND z.Longitude >= r.MinLong
                  AND CityType = 'D'
                  AND ZIPCodeType <> 'M'
                  AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles
                  /*
                        The above functions requires the Distance Assistant.
                        Also note that SQL Server caches the results so that this and the "SELECT RadiusAssistant"
                        functions are both only computed once.
                  */
            ORDER BY Distance
      END
ELSE
      SELECT -1 As ZIPCode
      --ZIP Code not found...

*** This is how my code is setup by adding the count(*) I can not return values and have not been able to make the Distance calculator work right because, I am not sure how to write the query****

***So if someone can get this to return below properly and add the distance code from above which is this code****
***THINGS THAT MUST WORK
***1) Return Distance
***2)Return MemberID
***3)Return Rowcount of the records returned
***IT will be a combination of both.

***Will Say thank you.
                                                z.ZIPCode, z.City,
                  dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
*****************************************************************************************
IF @RowCount > 0
            
      BEGIN
            SELECT  @COUNT = COUNT( *) , M.MemberID
            FROM Member  M
            WHERE PostalCode IN (
            (SELECT z.ZIPCode
                                    
            FROM       ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r
                              
            WHERE       z.Latitude <= r.MaxLat
                  AND z.Latitude >= r.MinLat
                  AND z.Longitude <= r.MaxLong
                  AND z.Longitude >= r.MinLong
                  AND CityType = 'D'
                  AND ZIPCodeType <> 'M'
                  AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles))
      
      IF @COUNT = 0
            /*Returns that there are no records for current search*/
            RETURN -1
      End

            
      
ELSE
Begin
/*  THIS WILL RETURN  ZIPCODE INVALID */
RETURN 2

***IF you need more information just post your question.******
0
Comment
Question by:mcsdguyian
2 Comments
 
LVL 4

Accepted Solution

by:
mikkelp earned 500 total points
ID: 10675386
You want to return the number of members in the resultset?

If you need the count in the resultset, there's only the ugly way of doing two selects, possibly in the same statement. The reason is - presumably - that when starting on the resultset, it's not determined how many rows will be returned. You need to check this first.

Otherwise, you can use the @@ROWCOUNT function, like this:

IF @@ROWCOUNT = 0
      Return -1
END

I think I would go with an output-parameter. Ie select memberid,distance in resultset and put @@rowcount in output parm.

but if you need the combination, there are various ways to go:
1. A double select
-------------------------
SELECT @Count = count(*) FROM Member M WHERE PostalCode in (your distance-select) ....
SELECT @Count as count, MemberID, distance FROM MemberM WHERE PostalCode in ....

2. A joined select
--------------------
SELECT a.Count, b.MemberID
FROM (select count(*) as Count from Member M Where Postalcode....) as a,
(select memberid,distance from memberM where.....) as b

3. Using a temporary table
------------------------------
CREATE TABLE #members (
MemberID int,
Distance real
)
INSERT INTO #members
SELECT MemberID, distance FROM Members M Where....

SELECT @count = @@rowcount

SELECT @count, memberID, Distance from #members
DROP TABLE #members

To get the distance, you would need to modify your query a little

SELECT  M.MemberID, Z.Distance
          FROM Member  M,
          (SELECT z.ZIPCode, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) as distance
          FROM      ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r
          WHERE      z.Latitude <= r.MaxLat
               AND z.Latitude >= r.MinLat
               AND z.Longitude <= r.MaxLong
               AND z.Longitude >= r.MinLong
               AND CityType = 'D'
               AND ZIPCodeType <> 'M'
               AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles) as Z
          WHERE M.PostalCode = Z.Zipcode


good luck

mikkelp
0
 

Author Comment

by:mcsdguyian
ID: 10682809
Thanks mikkelp

I guess I didn't need an actual count so I was able get it to do exactly what I needed, I just wanted to send back a return parameter.  This is what I did

DECLARE @ZIPCode nchar(5)
SET @ZIPCode = 90210

DECLARE @Miles REAL
SET @Miles = 5

DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @ZIPCode AND CityType = 'D'

if @RowCount > 0

      BEGIN
      SELECT  @RowCount as Zipcodes_Counted
            SELECT M.MemberID , Z.Distance AS D,M.Username
                FROM Member  M,
                (SELECT z.ZIPCode, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) as Distance
                FROM ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r
                WHERE z.Latitude <= r.MaxLat
                    AND z.Latitude >= r.MinLat
                     AND z.Longitude <= r.MaxLong
                    AND z.Longitude >= r.MinLong
                    AND CityType = 'D'
                    AND ZIPCodeType <> 'M'
                    AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles)as Z
                WHERE M.PostalCode = Z.Zipcode
      Order by Z.Distance
            
IF @@ROWCOUNT = 0
      --NO RECORDS FOUND...
      RETURN -1      
END

ELSE
      RETURN 2
      
      --NO VALID ZIPCode found...
Look for 2 other posts  called thanks1 and thanks2 and I will award 500 for each of those to you.
Thanks again
mcsdguyian

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question