Solved

Help With SQL Database Query

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

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

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