Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: Create View Issue


This question is answered.


Permlink Replies: 1 - Last Post: Mar 16, 2017 1:13 AM Last Post By: Lajos Juhasz Threads: [ Previous | Next ]
marcio costa

Posts: 1
Registered: 4/12/11
Create View Issue  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 15, 2017 5:31 PM
Hello !

I have a table with this fields:
table: client
- ID (integer, PK)
- NAME (string)
- IDUSR_DAY (integer, FK)
- IDUSR_NIGHT (integer, FK)

The fields IDUSR_DAY and IDUSR_NIGHT are foreign key to table USERS.
table: users
- IDUSR (integer, PK)
- NAME (string)

Im trying create a view to show the user name for both fields IDUSR_DAY and IDUSR_NIGHT, but I only get the user name for the field IDUSR_DAY;

Bellow my view:

CREATE VIEW X_VIEW(
ID,
NAME,
IDUSR_DAY,
NAME_DAY,
IDUSR_NIGHT,
NAME_NIGHT)

AS
select
client.id,
client.name,
client.idusr_day,
user.name,
client.idusr_night,
user.name

from client
join users on (users.idusr = client.idusr_day ) or (users.idusr = client.usr_night)
;

I appreciate any help about how to construct the correct view code;
Thank you

Edited by: marcio costa on Mar 15, 2017 5:34 PM
Lajos Juhasz

Posts: 705
Registered: 3/14/14
Re: Create View Issue [Edit]
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 16, 2017 1:13 AM   in response to: marcio costa in response to: marcio costa
marcio costa wrote:

CREATE VIEW X_VIEW(
ID,
NAME,
IDUSR_DAY,
NAME_DAY,
IDUSR_NIGHT,
NAME_NIGHT)

AS
select
client.id,
client.name,
client.idusr_day,
user.name,
client.idusr_night,
user.name

from client
join users on (users.idusr = client.idusr_day ) or (users.idusr =
client.usr_night)

You have to join the table users twice:

CREATE VIEW X_VIEW(
  ID,
  NAME,
  IDUSR_DAY,
  NAME_DAY,
  IDUSR_NIGHT,
  NAME_NIGHT)
 
AS
select 
  client.id,
  client.name,
  client.idusr_day,
  userD.name,
  client.idusr_night,
  userN.name
 
from client
  join users as userD on (usersD.idusr = client.idusr_day ) 
  join users as userN on (usersN.idusr = client.usr_night)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02