Solved

Complex Join

Posted on 2004-03-24
2
300 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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now