-1

I was trying to figure out how to insert data into a new join table using the column data from two other tables. This seemed at first simple, but wasn't immediately obvious now could I find the solution anyplace.

Example: I have tProfiles table:

select prof_id from tProfiles

prof_id

1
2
3
4
5
...

and table containing roles tRole

select roleid from tRole

roleid
1
2
3
4
5
6
7
...

and new tRoleByProfile needs all roles from tRole and all prof_id from tProfiles using an insert like this: insert into tRoleByProfile(RoleId, ProfileId)

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
user3663214
  • 39
  • 4

2 Answers2

0

Now I'm doing several joins here to get just the profiles with active users in them like this.

insert into tRoleByProfile(RoleId, ProfileId)
select distinct r.RoleId, p.prof_id from tRole r, tProfiles p
inner join tUserRoles ur on p.PROF_ID = ur.prof_id 
inner join tUsers u on u.user_id = ur.user_id
inner join tUserLogins ul on ul.user_id = u.user_id
where u.user_inactive_flg = 'N'

notice in the select I must use distinct on the Full Join. Without using Distinct would create lot's of duplicate data. See http://www.informit.com/articles/article.aspx?p=30875&seqNum=5 for more on Full Join.

Now my new join table has the exact data needed from the combined two tables.

user3663214
  • 39
  • 4
0

You possibly could have done...

INSERT INTO tRoleByProfile 
   SELECT r.RoleId, p.prof_id 
   FROM tRole r
   JOIN tProfiles p;

Joining two tables without any condition will yield a result set that incorporates every combination of the records from both tables. In other words, every profile would receive every role.

To filter what rows are included in the Cartesian join (that is a join with no relationship), filter either side of the JOIN above by making them a separate select, as in...

INSERT INTO tRoleByProfile 
SELECT r.RoleId, p.prof_id 
FROM tRole r
JOIN (SELECT prof_id 
      FROM tProfiles 
      WHERE blah...)
Steven Ackley
  • 593
  • 7
  • 31
Rodney P. Barbati
  • 1,883
  • 24
  • 18
  • Rodney my query does not include all combinations once I added the distinct to it ... see below. I tested this out ... without distinct I get all possible rows, but with distinct I got exact unique results and it worked as I intended... ie. not inserting duplicates etc. – user3663214 Feb 28 '17 at 03:39
  • After rereading your comment is misinterpreted your answer. Your solution looks good and thanks for the solution. Not sure though why I'm getting a downvote. Though this seems like a simple solution to a simple problem I scoured the interweb and stackoverflow for quite a while to find no answer sadly. Thus this post. I proved this works and is both efficient and avoids duplicates... anyways. – user3663214 Feb 28 '17 at 04:03