CODEWITHSUNDEEP
mysqlsqldatabasejoin
Ryan M
Ryan M

Reputation: 45

table with multiple foreign keys joining 1 foriegn key in mysql

Hi I have a database table with the following information :

owner.primaryitowner, ([email protected])
owner.secondaryitowner, 
owner.primarybusinessowner, 
owner.secondarybusinessowner
users.username (email [email protected])
users.displayname (e.g. Bob Smith)

The issue, is the owners are only stored as emails. There is another table I normally

inner join users on users.username = owner.primaryitowner to get users.displayname so the data reads correctly.`

I am able to do this

select u.displayname
from users u
inner join owners o on
    o.primaryitowner = u.username
    or o.secondaryitowner = u.username
    or o.primarybusinessowner = u.username
    or o.secondarybusinessowner = u.username

The issue is I need to have unique columns not all into one column.

PS I cannot change the database I am only a report writer.

Thanks so much in advance

Upvotes: 1

Views: 130

Answers (2)

Bradley M Handy
Bradley M Handy

Reputation: 613

Does this work for your needs? When you come at it from the point of view of the owner table, it's easier to grab all of the displayname's as separate columns for a single owner record. Going the other way will pull information from muliple owner records for the same user. If that's what you want, then this won't work.

select prim_o.displayname as "Primary IT Owner",
    sec_o.displayname as "Secondary IT Owner",
    prim_bo.displayname as "Primary Business Owner",
    sec_bo.displayname as "Secondary Business Owner"
from owner o
   inner join users prim_o (o.primaryitowner = prim_o.username)
   inner join users sec_o (o.secondaryitowner = sec_o.username)
   inner join users prim_bo (o.primarybusinessowner = prim_bo.username)
   inner join users sec_bo (o.secondarybusinessowner = sec_bo.username)

Upvotes: 0

Adam Wenger
Adam Wenger

Reputation: 17540

You will want to join each column of email from users into owners

SELECT u.displayname AS userName
   , po.displayName AS PrimaryItOwnerUsernName
   , so.displayName AS SecondaryIdOwnerUserName
FROM users AS u
INNER JOIN owners AS po on u.primaryitowner = po.username
INNER JOIN owners AS so ON u.secondaryitowner = so.username
...
WHERE u.UserName = 'Ryan J Morse'

When you join into the owners table (aliased) multiple times, this allows you to change the emails stored in users into the display names you will need for your report.

Upvotes: 3

Related Questions