3 Replies - 818 Views - Last Post: 04 March 2010 - 12:45 AM Rate Topic: -----

#1 Guest_Fixed_Width_Field*


Reputation:

SQL "How To" involving multiple foreign keys

Posted 01 March 2010 - 05:35 AM

Hi,

I'm writing some automated scripts that will take dumps of users in a database and compare the information to an internal audit DB we keep. I'm trying to take a shortcut here by getting the data in an easy to parse format. The issue is that, in order to audit DB permissions, I have to dump one line per permission. I'll illustrate the relationships:

User table
user_id
...
...

User Permission table
user_id
permission_group


User Permission Group table
group_id
... more stuff...


Basically, if I do a query, I can get one line per entry in the user permission table, but what I would like is to have each permission group populate at the end of a row.... An example of my current output:

user1 5
user1 7
user1 11
user2 10
user2 11

But what I actually want is....

user1 5 7 11
user2 10 11

Thanks in advance for the help.

Is This A Good Question/Topic? 0

Replies To: SQL "How To" involving multiple foreign keys

#2 gregoryH   User is offline

  • D.I.C Addict
  • member icon

Reputation: 60
  • View blog
  • Posts: 656
  • Joined: 04-October 06

Re: SQL "How To" involving multiple foreign keys

Posted 02 March 2010 - 02:06 AM

Hi

I think that you need to use a join type query. One way is like this:

select t1.col1, t1.col2, t2.col4
from table1 t1 inner join table2 t2 on t2.col1 = t1.col2


or if you prefer this way

select t1.col1, t1.col2, t2.col4
from table1 t1,join table2 t2
where t2.col1 = t1.col2


Can you show us your work so we can help you more?
Was This Post Helpful? 0
  • +
  • -

#3 Guest_Fixed_Width_Field*


Reputation:

Re: SQL "How To" involving multiple foreign keys

Posted 03 March 2010 - 01:10 AM

Hi,

My query was already composed of joins, the issue was that I was getting multiple records for users if they were in multiple permission groups. The table structure is such that to include a user in a group, there is a table that has an row entry for each group the user is in. I want to convert this to have all the groups in one row.

Example of joined data
user1 group3
user1 group5
user1 group9
user2 group1
user2 group3

Example of what I'd like

user1 group3 group5 group9
user2 group1 group3


The query is something like this

select u.user_id, us.group_id from user as u
inner join user_security as us
ON u.user_id = us.user_id

So, all the data is returned, but not in the format I want.

I really want to know how to do this, because I ended up writing a C++ utility to parse the results of the query, and if I didn't have to reinvent the wheel, I'd like to save the time next go around.

Thanks again for any help.
Was This Post Helpful? 0

#4 gregoryH   User is offline

  • D.I.C Addict
  • member icon

Reputation: 60
  • View blog
  • Posts: 656
  • Joined: 04-October 06

Re: SQL "How To" involving multiple foreign keys

Posted 04 March 2010 - 12:45 AM

Hello again

I am not sure if its just me, but your original post didn't show any SQL for us to look at.

Now I have seen your code I understand exactly what you are trying to pull out. The problem is fact that for each individual user name, you want a listing of the parameters in a single line.

Because this data is dynamic, its actually a lot harder to do from a simple SQL statement. One possible way would be to create a stored procedure (again I am assuming that your running a database with this capability).

you have to run a query that extracts data from each table and returns a list that you build (n code) into a comma separated string which can be loaded into a result table.

Are you using a database that can do this and can you write a stored procedure?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1