Table View double foreign key.

[Former Member]

Hello,

I'm facing a problem about table view. My problem is the following:

I have

-

---

Table Car ( id_car, id_color_out, id_color_rims, id_color_bumper, model)

Table Color( id_color, name_color)

-

---

So, id_color_out, id_color_rims, id_color_bumper are foreign keys of table color.

In native sql the view was something like this:

-

---

select id_car, c1.name_color, c2.name_color, c3.name_color, model

from car inner join

color as c1 on c1.id_color = car.id_color_out inner join

color as c2 on c2.id_color = car.id_color_rims inner join

color as c3 on c3.id_color = car.id_color_bumper

-

---

I don't know how to do this in a VIEW. Can anyone help me?

Thanks in advance!

Edited by: Paulo Santos on Sep 11, 2008 12:09 PM

[Former Member]

Hi Paulo Santos ,

Go to se11.

Select the 'View' Radio Button

enter View name(ZCar_color_view) click enter and (Select Database View)enter.

Click on create

Entert Car and Color in the table panel window

Give the condtions in the join conditions panel(as given in the inner join)

Give the field names that you want to see after the join

Save and activate.

Automatically data will be populated as joined.

Hope this helps you.

Regards,

Rama.P

[Former Member]

hello Rama Chary ,

first thank you for your fast response!

unfortunatly your solution doesn't work.

look at this example.

Car (1,1,2,3,"Ferrari")

Color(1,"Red")

Color(2,"Blue")

Color(3,"Black")

The view should return:

(id_car, c1.name_color, c2.name_color, c3.name_color, model)

1, Red, Blue, Black, Ferrari

I believe that your solution doesn't return any value because in my join conditions I have:

color.id_color = car.id_color_out

color.id_color = car.id_color_rims

color.id_color = car.id_color_bumper

and the result is

color.id_color =1

color.id_color =2

color.id_color =3

so this can't work because color.id_color can't be equal to 1 AND 2 AND 3...

In vision fields I inserted:

VISION_FIELD -

---

TABLE NAME -

---

FIELD NAME

ID CAR -

---

car -

---

id_car

COLOR OUT -

---

car -

---

id_color_out

COLOR RIMS -

---

car -

---

id_color_rims

COLOR BUMPER -

---

car -

---

id_color_bumper

MODEL -

---

car -

---

model

Is this correct?

Edited by: Paulo Santos on Sep 11, 2008 1:05 PM

Edited by: Paulo Santos on Sep 11, 2008 1:56 PM