1. Georg Lacher
  2. Valentina Studio
  3. 土, 7月 25 2020, 03:40 PM
  4.  メールで購読
Hello!
Valentina Studio is able to access my SQL database. I created a diagram with links between the tables. The Data Editor shows the records of the selected tables, but not - as shown in the introduction video - the records in the linked tables. What can be the reason?
Thanks
Georg
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
Hello Georg,

Not clear - what exactly does each row of this subform show?

If you need to present data from multiple rows as value of one cell (e.g. all actors of the film) GROUP_CONCAT can be used.

Something like that - it takes all records of table with films and adds "cast" column to each film.
SELECT *,
( SELECT GROUP_CONCAT( a.last_name SEPARATOR ', ' ) FROM film_actor fa
INNER JOIN actor a ON a.actor_id = fa.actor_i AND fa.film_id = f.film_id ) AS "cast"
FROM film f
コメント
There are no comments made yet.
Georg Lacher 承諾済みの回答
Hello Sergey,

no, I don't want to put data from multiple rows in one cell. I try to explain it better now:

The table ZUORDNUNG has a foreign key field which points to the table FILME. The other Fields show the directors, screenwriters, authors, cameramen and actors of the specifield movie.

The problem is that the table ZUORDNUNG has not one foreign key field which points to the table PERSONEN, but five! There is one column for the directors, one column for the screenwriters, one column for the book authors, one for the cameramen and one for the actors. I don't know how to put this in an SQL command.

If you look at my last picture you can see the main form (FILME) and the subform (ZUORDNUNG) below. The subforms show the foreign keys which point to the table PERSONEN. But, of course, they should show the names.

Thank you!
Georg
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
Hello Georg,

So Zuordnung acts like a kind of M:M link between Filme and Personen.

Let's start with writing a query in the SQL Editor.
If there are 5 FKs then there should be 5 joins (you have NULLs in the FK, so it must be LEFT JOIN).

I created a similar database structure films-assignments-persons

Note, "person" in each join must have an alias to get the "name" field from each join.


SELECT
`a`.`film_number`,
`dir`.`name`as`Director`,
`scr`.`name`AS`Script`,
`cam`.`name`AS`Cameraman`,
`act`.`name`AS`Actor`
FROM
`assignments` `a`
LEFT JOIN `persons` `dir`
ON `a`.`director` = `dir`.`person_id`
LEFT JOIN `persons` `cam`
ON `a`.`cameraman` = `cam`.`person_id`
LEFT JOIN `persons` `scr`
ON `a`.`script` = `scr`.`person_id`
LEFT JOIN `persons` `act`
ON `a`.`director` = `act`.`person_id`
WHERE `film_number` = 1


Now we can add this query on the project tab, replacing "film_number" value with the parameter name.

I added a screenshot for each step:
1. Create pFilmID parameter
2. Write a query
3. Add TableView to the form
4. Map pFilmID parameter to the film_id field of the main form
5. Execute form
添付ファイル
コメント
There are no comments made yet.
  • ページ :
  • 1
  • 2


There are no replies made for this post yet.
However, you are not allowed to reply to this post.