?
Solved

Help With SQL Database Query

Posted on 2014-03-16
2
Medium Priority
?
239 Views
Last Modified: 2014-03-17
I have a SQL Data Source which queries two separate tables.  
Table "Locations" contains columns: UserId, City, State, ID
Table "Userprofiles": contains Columns UserId, City  and various other columns.
There is a foreign Key  connecting the UserId of both tables.

I want the user to be able to select a city to query both tables and retrieve User Information based on the city.  The userProfile table  contains all the Users Information and the Locations table contains locations where the user has lived.  Each user could have many cities.  

In the site if a person selects a city for example "Houston"  then I want to be able to select all users that have lived in Houston.  

Im am not sure how to create the query properly to query the users from the userprofiles table and the same users from locations table and retrieve the result.  

I hope this is not to confusing.  THANKS




Each table has UserId


<asp:SqlDataSource ID="SqlDataSource3" runat="server"
            ConnectionString="<%$ ConnectionStrings:dbMyCMSConnectionString %>"
           SelectCommand="SELECT UserProfiles.UserId, UserProfiles.City, Locations.city, Locations.UserId, Locations.id FROM UserProfiles CROSS JOIN Locations WHERE (UserProfiles.City = @City)">
           <SelectParameters>
               <asp:ControlParameter ControlID="city" Name="City" PropertyName="Text" Type="String"  />
           </SelectParameters>
                      </asp:SqlDataSource>
0
Comment
Question by:misnstt
2 Comments
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 1000 total points
ID: 39933014
It should be

SELECT UserProfiles.UserId, UserProfiles.City, Locations.city, Locations.UserId, Locations.id FROM UserProfiles INNER JOIN Locations On Locations.UserID = UserProfiles.UserID And Locations.City = @City

You don't want to compare UserProfiles.City to @City, as you don't only want to filter users by the city the live in right now, but where they ever have lived according to Locations. Also A cross join is wrong as it joins any record of both tables.

Bye, Olaf.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 total points
ID: 39933239
You have a field for city in both tables which isn't good design as you could get mismatches (or one of these is redundant)

If there are mismatches between the tables then you would want an 'OR' condition like this:
SELECT
      UserProfiles.UserId
    , UserProfiles.City
    , Locations.city
    , Locations.UserId
    , Locations.id
FROM UserProfiles
      INNER JOIN Locations
            ON Locations.UserID = UserProfiles.UserID
                  AND (Locations.City = @City
                  OR UserProfiles.City = @City)

Open in new window

Please also note Olaf's warning about use of cross join - it is indeed wrong. See below:
    CREATE TABLE UserProfiles
    	([UserId] int, [City] varchar(12))
    ;
    	
    INSERT INTO UserProfiles
    	([UserId], [City])
    VALUES
    	(1, 'New York'),
    	(2, 'Tokyo')
    ;
    
    CREATE TABLE Locations
    	([ID] int, [UserId] int, [City] varchar(10), [State] varchar(11))
    ;
    	
    INSERT INTO Locations
    	([ID], [UserId], [City], [State])
    VALUES
    	(1, 1, 'London', 'England'),
    	(2, 2, 'Tokyo', 'Japan')
    ;

**Query 1**:

    -- use of CROSS JOIN is wrong as it generates records that should not exist
    
    DECLARE @City AS varchar(20)
    SET @City = 'Tokyo'
    
    SELECT
          UserProfiles.UserId
        , UserProfiles.City
        , Locations.city AS loc_city
        , Locations.UserId AS loc_userid
        , Locations.id
    FROM UserProfiles
          CROSS JOIN Locations
    WHERE (UserProfiles.City = @City)
    

**[Results][2]**:
    
    | USERID |  CITY | LOC_CITY | LOC_USERID | ID |
    |--------|-------|----------|------------|----|
    |      2 | Tokyo |   London |          1 |  1 |
    |      2 | Tokyo |    Tokyo |          2 |  2 |


**Query 2**:

    -- using an INNER JOIN produces the correct number of records to evaluate
    
    DECLARE @City AS varchar(20)
    SET @City = 'Tokyo'
    
    SELECT
          UserProfiles.UserId
        , UserProfiles.City
        , Locations.city AS loc_city
        , Locations.UserId AS loc_userid
        , Locations.id
    FROM UserProfiles
          INNER JOIN Locations
                ON Locations.UserID = UserProfiles.UserID
                      AND (Locations.City = @City
                      OR UserProfiles.City = @City)
    	
    	
    

**[Results][3]**:
    
    | USERID |  CITY | LOC_CITY | LOC_USERID | ID |
    |--------|-------|----------|------------|----|
    |      2 | Tokyo |    Tokyo |          2 |  2 |



  [1]: http://sqlfiddle.com/#!3/85e65/2

  [2]: http://sqlfiddle.com/#!3/85e65/2/0

  [3]: http://sqlfiddle.com/#!3/85e65/2/1

Open in new window

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

579 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