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

x
?
Solved

Help With SQL Database Query

Posted on 2014-03-16
2
Medium Priority
?
238 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

916 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