Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Complex Join

Posted on 2004-03-24
2
Medium Priority
?
325 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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

886 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