sql create view in one to many relation

Question

I want to create a view in a one-to-many relation. Here are my relations:

(a -* b)
(a -* c)

I want to create a view to have this data:

• a1 - (all b's related to a1 + all c's related to a1)

I don't want something like (a1,b1,c1 - a1,b1,c2 ,...). I want data to be:

(ID,DESCRIPTION1,DESCRIPTION2,DESCRIPTION3)

in one record.

For example, if in table a I have data: (1,2,3)

and in table b I have (10,11,12 with fk to 1 in a and 20,21,22 with fk to 2 in a)

and in table c I have (100,101,102 with fk to 1 in a)

I want result like this in one row:

1,10,11,12,100,101,102 for 1

and something like this for 2 , 3 ,...

i think i need "single row subquery example" but i can't find any example , can you help me plz ?

How can I create this view?

Answer 1

As noted in a comment I made, the notation used to describe the relations is not standard.

I think it means you have a relation A that has a one-to-many relationship with rows in relation B, and that also has a separate one-to-many relationship with rows in relation C.

The result relation (the view) should ideally have one row for each item in A, along with the corresponding rows from B and the corresponding rows from C - but there should not be a Cartesian product of the rows in B and C w.r.t the the row in A.

I'm assuming your relations are:

A(A1, A2, A3) - Primary Key (A1)
B(B1, B2, B3) - Foreign Key (B1) References A(A1)
C(C1, C2, C3) - Foreign Key (C1) References A(A1)

If you follow C J Date's views, what you need is a view with a pair of RVA - relation-valued attributes. That is, you would have a table structure analogous to:

+------+------+------+------------------+-----------------+
|      |      |      |  +------+------+ | +------+------+ |
|  A1  |  A2  |  A3  |  |  B2  |  B3  | | |  C2  |  C3  | |
|      |      |      |  +------+------+ | +------+------+ |
+------+------+------+------------------+-----------------+
|      |      |      |  +------+------+ | +------+------+ |
|      |      |      |  |  b21 |  b31 | | |  c42 |  c13 | |
|  a11 |  a21 |  a31 |  |  b22 |  b32 | | |  c52 |  c23 | |
|      |      |      |  |  b23 |  b33 | | |  c62 |  c13 | |
|      |      |      |  |      |      | | |  c72 |  c23 | |
|      |      |      |  +------+------+ | +------+------+ |
+------+------+------+------------------+-----------------+

This is a very neat way of representing what you are after. Unfortunately, AFAIK, SQL does not support this notation. The nearest approach is likely to be two independent outer joins, which tends to generate a lot of rows (12 in the example):

SELECT A.A1, A.A2, A.A3, B.B2, B.B3, C.C2, C.C3
  FROM A LEFT OUTER JOIN B ON A.A1 = B.B1
         LEFT OUTER JOIN C ON A.A1 = C.C1

---

You say:

I want data to be:

(ID,DESCRIPTION1,DESCRIPTION2,DESCRIPTION3)

in one record.

For example, if in table a I have data: (1,2,3)

and in table b I have (10,11,12 with fk to 1 in a and 20,21,22 with fk to 2 in a)

and in table c I have (100,101,102 with fk to 1 in a)

I want result like this in one row:

1,10,11,12,100,101,102 for 1

You want a view with 4 columns according to the "I want the data to be" statement, yet the result you show has 7 values showing - because there are three records in B corresponding to the record with an ID of 1 in A, and similarly because there are three records in C too. It is not clear what value was in the Description1 from A - it seems to be missing. How many columns would you want if there were 6 rows in B and 10 in C that corresponded to row 1 in A? And how does this relate to the number of columns for the record with ID 2 in A (with 4 rows in B and 3 rows in C that match)?

If you requested a result like:

 1,Note1,{10,11,12},{100,101,102}

where the braces surround a list of some sort, then your result has indeed got four columns. Further, if your DBMS supports a GROUP_CONCAT operation, you may even be able to write a query that produces the result.

You will learn that if you do not express the requirements of your query precisely, you get quasi-random results - or results other than what you really wanted, at any rate. When you get the query requirements expressed precisely, the whole process is much easier.

In the systems that support GROUP_CONCAT, you would get one list using:

SELECT B.ID, GROUP_CONCAT(B.Description2) AS Description2
  FROM B
 GROUP BY B.ID

You then write your overall query joining two of these expressions to A:

SELECT A1.ID, A1.Description AS Description1,
       B2.Description2, C3.Description3
  FROM A AS A1 LEFT OUTER JOIN
       (SELECT B.ID, GROUP_CONCAT(B.Description) AS Description2
          FROM B
         GROUP BY B.ID) AS B2 ON A1.ID = B2.ID
         LEFT OUTER JOIN
       (SELECT C.ID, GROUP_CONCAT(C.Description) AS Description3
          FROM C
         GROUP BY C.ID) AS C3 ON A1.ID = C3.ID

To make that into a view, apply the appropriate 'CREATE VIEW' prefix.