SELECT from 3 tables - 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)

SELECT from 3 tables

Author

Topic

eddyuk
Starting Member

4 Posts

Posted - 2011-01-06 : 10:30:25

Hello!

I have 3 tables:
1. Agents - with following fields:
AgentId
AgentName
AgentCallCode

2. Positions - with following fields:
PositionId
PositionTitle

3. AgentPositions - with following fields:
AgentId
PositionId

Table Positions contains 3 records:
PositionId PositionTitle
---------- -------------
1 Main entrance
2 Lobby
3 Roof

When agent is being added to Agents table, by default agent assigned to all positions listed in Positions table,
but in order not to keep for each agent 3 records in AgentPositions table, I rather to insert records only when
agent assigned only to some positions, meaning, if i dont have records for agent in AgentPositions table, agent
assigned to all 3 positions.

Example:

Agents table records:
AgentId AgentName AgentCallCode
------- --------- -------------
1 Dan S*** 9080
2 Raven S* 9081
3 James S* 9083

AgentPositions table records:
AgentId PositionId
------- ----------
2 2
2 3
3 3

Positions records are as listed above.

Here we can see that agent Dan assigned to all 3 positions (since he don't have records in AgentPositions table)
Agent Raven assigned to lobby and roof and agent James assigned to roof.

My question is:
I need to select all agent's with assigned positions. How do i return rows for agent Dan in same query.
I need the result to look like this

AgentId AgentName PositionTitle
------- --------- -------------
1 Dan Main entrance
1 Dan Lobby
1 Dan Roof
2 Raven Lobby
2 Raven Roof
3 James Roof

Hope that made sence :)

Thanks in advanced to all!!!

Luismmr_cu
Starting Member

2 Posts

Posted - 2011-01-06 : 11:09:32

hi,

try this, it should work:

select
a.AgentId,
a.AgentName,
p.PositionTitle
from Agents a
join AgentPositions ap on a.AgentId=ap.AgentId
join Positions p on ap.PositionId=p.PositionId

union all

select
a.AgentId,
a.AgentName,
p.PositionTitle
from Agents a
cross join Positions p
where a.AgentId not in ( select AgentId from AgentPositions)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources