Solved

Help With SQL Database Query

Posted on 2014-03-16
2
235 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
[X]
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
2 Comments
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 250 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 250 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

734 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