Solved

Help With SQL Database Query

Posted on 2014-03-16
2
236 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

628 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