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)">
<asp:ControlParameter ControlID="city" Name="City" PropertyName="Text" Type="String" />