Associating users - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Associating users

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-11-29 : 03:21:56

Hi,

I have 4 tables

Users
userid
fst_name
lst_name

State
stateid
statename

City
Cityid
CityName
stateid

UserStateCity
uscid
userid
stateid
cityid

I want to write a query to populate UserStateCity based on the following logic :
Associate user with userid = 20 to state=80 and all cities in that state. There are around 50 cities associated with State=80. So in Total, 50 rows needs to be created in UserStateCity in the following manner :

For eg:
1   20   80   1
2   20   80   2
3   20   80   3

and so on

thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-29 : 03:31:15

does users relate to state at all? or do you mean you just want to explicitly put userid 20 into the userstatecity table for state 80?

i.e.
select 20, s.stateid, c.cityid
from state s join city c on s.stateid = c.stateid
where s.stateid = 80

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-29 : 03:39:16

This code will give expected result:-
CREATE TABLE #Users
(
userid int,
fst_name varchar(50),
lst_name varchar(50)
)

CREATE TABLE #State
(
stateid int ,
statename varchar(50)
)

CREATE TABLE #City
(
Cityid int,
CityName varchar(50),
stateid int
)

CREATE TABLE #UserStateCity
(
uscid int IDENTITY(1,1),
userid int,
stateid int,
cityid int
)

INSERT into #Users values (1,'F1','L1')
INSERT into #Users values (2,'F2','L2')
INSERT into #Users values (3,'F3','L3')
INSERT into #Users values (4,'F4','L4')
INSERT into #Users values (5,'F5','L5')
INSERT into #Users values (20,'F20','L20')

INSERT into #State values (20,'S1')
INSERT into #State values (40,'S2')
INSERT into #State values (60,'S3')
INSERT into #State values (80,'S4')

INSERT into #City values (1,'C1',40)
INSERT into #City values (2,'C2',60)
INSERT into #City values (3,'C3',80)
INSERT into #City values (4,'C4',60)
INSERT into #City values (5,'C5',80)
INSERT into #City values (6,'C6',80)
INSERT into #City values (7,'C7',60)
INSERT into #City values (8,'C8',80)

INSERT INTO #UserStateCity (userid,stateid,cityid)
SELECT 20,
#s.stateid,
#c.cityid
FROM #State #s
INNER JOIN #City #c
ON #c.stateid=#s.stateid
WHERE #s.stateid=80

SELECT * FROM #UserStateCity

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources