How to combine data from different queries & sort

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)

How to combine data from different queries & sort

Author

Topic

GenNS
Starting Member

11 Posts

Posted - 2011-11-09 : 11:48:46

Hi,

I've been searching through the forums and other resources online trying to figure this out, but I'm not even sure what it's called so I'm not having much luck. Here's the scenario:

I have db of Stores that needs to be accessed by PR agents so that they can post sale updates, etc. For the purposes of this issue, we have the following tables:

stores:
id
name

clients: (a client may own one or more stores)
id
name

prAgents:
id
name

prAccess:
clientID (id in clients)
storeID (id in stores)
agentID (id in prAgents)

Now, the trick here is that some stores give their PR people access to all of their stores, which is stored as 0 (zero) in storeID in the prAccess table.

But other clients only give the agents access to a particular store, so that might be something like 1012 being stored in storeID in the prAccess table.

What I need to do is make a list of stores that a PR person has access to, across all clients, but sorted by the store name.

And this is my confusion, because I don't know how to get ALL the stores from a client where the storeID is 0, and only the specific stores from other clients that have granted specific store access to an agent, and sort the entire result set by store name.

Any help on this would be greatly appreciated. Thanks in advance!

Genns

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 12:38:17

[code]
SELECT ag.name,c.name,s.name
FROM prAccess a
inner join prAgents ag
on ag.id = a.agentID
inner join clients c
on c.id = a.clientID
inner join stores s
on (s.id = a.storeID
or a.storeID=0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GenNS
Starting Member

11 Posts

Posted - 2011-11-09 : 14:42:30

Thanks visakh16, but this actually returns everything in the stores database, even after changing it as follows (@agent being the ID of the PR person in question and active being another field in the Stores table that flags a store as active / valid or not):

SELECT DISTINCT ag.name,c.name,s.name
FROM prAccess a
inner join prAgents ag
on ag.id = a.agentID
inner join clients c
on c.id = a.clientID
inner join stores s
on (s.id = a.storeID
or a.storeID=0)
WHERE a.agentID = @agent AND s.active <> 0
ORDER BY s.name

GenNS
Starting Member

11 Posts

Posted - 2011-11-09 : 15:20:43

Ok, it looks like this is the way to do it, starting with visakh16's suggestion:

SELECT DISTINCT ag.name,c.name,s.name
FROM prAccess a
inner join prAgents ag on ag.id = a.agentID
inner join clients c on c.id = a.clientID
inner join stores s on s.id = a.storeID
WHERE a.agentID = @agent AND s.active <> 0

UNION ALL

SELECT DISTINCT ag.name,c.name,s.name
FROM prAccess a
inner join prAgents ag on ag.id = a.agentID
inner join clients c on c.id = a.clientID
inner join stores s on s.clientID = c.id
WHERE a.agentID = @agent AND s.active <> 0 AND a.storeID = 0

ORDER BY s.name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources