SQL Query to get results from various tables

Question

I have a table called Users that stores generic user information (user id, first, last name, email id, phone number, is active, etc.). User Id is primary key.

I have a second table that stores the user's condo unit numbers (one to many relationship). One user can have one or more condos. User Id is secondary key.

UserID UnitNumber
    1      2A
    1      2B
    2      8H

I have a third table that stores the user's parking spot numbers (one to many relationship). One user can have zero or more parking spots. User Id is secondary key.

UserID ParkingSpot
1      104
1      105
1      208
2      205
2      206

How can I write a query that will return the user information plus the condo unit numbers and parking spot numbers for each user?

What I am looking to obtain is something like this:

UserID  FirstName  LastName  Email         Phone         Units    ParkingSpots
1       John       Smith     john@xyz.com  123-456-7890  2A, 2B   104, 105, 208
2       Mike       Allen     mike@xyz.com  456-789-0123  8H       205, 206

Thanks!

http://stackoverflow.com/questions/3197626/concatenate-multiple-rows — Fionnuala, Jan 05 '12 at 21:00

score 0 · Answer 1 · answered Jan 05 '12 at 21:08

0

You can select from multiple tables using the primary key as:

SELECT Users.UserID, FirstName, LastName, Email, Phone, Units, ParkingSpots
FROM Users, Condos, ParkingUnit
WHERE Users.UserID == Condos.UserId
and Users.UserID == ParkingUnit.UserID

answered Jan 05 '12 at 21:08

Shraddha

2,337
1
16
14

score 0 · Answer 2 · answered Jan 05 '12 at 21:10

You can do this, but your going to pay for it in the long run. Its probably going to be faster to do this in the application.

create table #user (UserID int, FName varchar(max))
insert into #user values (1,'Jeremy')
insert into #user values (2,'Gisele')


create table #property (UserID int, UnitNumber varchar(2))
insert into #property values (1,'2A')
insert into #property values (1,'2B')
insert into #property values (2,'3A')
insert into #property values (2,'3B')

create table #parkingspot (UserID int, ParkingSpot int)
insert into #parkingspot values (1,104)
insert into #parkingspot values (1,105)
insert into #parkingspot values (2,106)

;with Info as 
(
select *
,Units = (select UnitNumber + ',' from #property where UserID = u.UserID for xml     path('') )
,ParkingSpots = (select cast(ParkingSpot as varchar(max)) + ',' from #parkingspot where     UserID = u.UserID for xml path('') )
from #user u
)
select *,StripUnitComma = case when LEN(Units) > 0 then Left(Units,LEN(Units)-1) else     Units end 
from Info

(also, I guarantee someone will put it back into a table structure from the string later)

score 0 · Accepted Answer · answered Jan 05 '12 at 21:12

0

You can use the STUFF function

SELECT 
    UserID,
    FirstName,
    LastName,
    Email,
    Phone,
    STUFF((SELECT ',' + n.UnitNumber FROM dbo.Units n WHERE n.UserID = u.UserID FOR XML PATH('')), 1, 1, ''),
    STUFF((SELECT ',' + p.ParkingSpot FROM dbo.Parking p WHERE p.UserID = u.UserID FOR XML     PATH('')), 1, 1, '')
FROM Users u

answered Jan 05 '12 at 21:12

msmucker0527

5,164
2
22
36

Thanks a lot! I did not know about STUFF! This works perfectly! Gracias. :) – saz Jan 05 '12 at 21:26

SQL Query to get results from various tables

3 Answers3