I am hoping there is a simple way to accomplish this selection

Question

I am hoping there is a simple way to accomplish this selection

meichner

SSCertifiable

Points: 5563
More actions
January 20, 2012 at 4:33 pm

#254034

I need to select certain data, but I am not sure of the best way to perform the select. Given the following tables (I would present the exact definitions, but I don't have access to them at the moment - sorry). Therefore I am just going to highlight the important fields.
UserGroups
Primary Key GroupCode
Description VarChar(50)
Users
Primary Key UserID
Foreign Key GroupCode
UserName VarChar(50)
Menu Items
Primary Key MenuID
Foreign Key GroupCode
MenuName VarChar(50)
I want to be able to display every possible combination of UserName and MenuName for a specific Group ordered by UserName and MenuName.
For instance, if I have a group called Sales, I want to display each of the Users in the Sales Group along with each of the Menu Names.
Given the following data in the Users Table:
Username[\u] GroupCode[\u]
John Smith Sales
Jane Austin Sales
Given the following data in the Menu Items Table:
MenuName [\u] GroupCode[\u]
Sales Entry Sales
Sales Inquiry Sales
I would like the output to be:
John Smith Sales Entry
John Smith Sales Inquiry
Jane Austin Sales Entry
Jane Austin Sales Inquiry
Any help would be much appreciated. Again, I am sorry that I didn't have access or time to supply the exact table definitions.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

meichner (1/20/2012)
UserGroups
Primary Key GroupCode
Description VarChar(50)
Users
Primary Key UserID
Foreign Key GroupCode
UserName VarChar(50)
Menu Items
Primary Key MenuID
Foreign Key GroupCode
MenuName VarChar(50)
I want to be able to display every possible combination of UserName and MenuName for a specific Group ordered by UserName and MenuName.

Roughly:

SELECT

ug.GroupCode,

u.UserName,

m.MenuName

FROM

UserGroups AS ug

JOIN

Users AS u

ONug.GroupCode = u.GroupCode

JOIN

[Menu Items] AS m

ONug.GroupCode = m.GroupCode

/*Note the lack of connection between u and m*/

ORDER BY

u.UserName,

m.MenuName

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

meichner SSCertifiable Points: 5563 More actions · Answer 2

Evil Kraig F (1/20/2012)
meichner (1/20/2012)
UserGroups
Primary Key GroupCode
Description VarChar(50)
Users
Primary Key UserID
Foreign Key GroupCode
UserName VarChar(50)
Menu Items
Primary Key MenuID
Foreign Key GroupCode
MenuName VarChar(50)
I want to be able to display every possible combination of UserName and MenuName for a specific Group ordered by UserName and MenuName.
Roughly:
SELECT
ug.GroupCode,
u.UserName,
m.MenuName
FROM
UserGroups AS ug
JOIN
Users AS u
ONug.GroupCode = u.GroupCode
JOIN
[Menu Items] AS m
ONug.GroupCode = m.GroupCode
/*Note the lack of connection between u and m*/
ORDER BY
u.UserName,
m.MenuName

Thanks for the response. I am kind of embarrassed by the question. I am re writting a fairly dumb overly complex process that I wrote a few years ago. For some reason when I posted this I had the idea in my head that the query was some sort of very complex cartesian product of the tables. I don't know why. As I was driving home from work realized that I was over thinking the query.

I do have a question about your note. Am I correct that if I had a connection between the 'u' and 'm' that I would not have gotten the results that I wanted?

Thanks

ps. I will test out all of this out at work on Monday. I do appreciate the help.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

meichner (1/20/2012)
I do have a question about your note. Am I correct that if I had a connection between the 'u' and 'm' that I would not have gotten the results that I wanted?

Depends on the connection. Since you're looking for a semi-cartesian product though, I just wanted to point out that you were hanging them both off the main table, instead of treating them like a hierarchy.

ps. I will test out all of this out at work on Monday. I do appreciate the help.

My pleasure. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

meichner SSCertifiable Points: 5563 More actions · Answer 4

Evil Kraig F (1/22/2012)
meichner (1/20/2012)
I do have a question about your note. Am I correct that if I had a connection between the 'u' and 'm' that I would not have gotten the results that I wanted?
Depends on the connection. Since you're looking for a semi-cartesian product though, I just wanted to point out that you were hanging them both off the main table, instead of treating them like a hierarchy.
ps. I will test out all of this out at work on Monday. I do appreciate the help.
My pleasure. 🙂

That did the trick.

Thanks so much.