1. Georg Lacher
  2. Valentina Studio
  3. Saturday, July 25 2020, 03:40 PM
  4.  Subscribe via email
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
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
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
Attachments (5)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
Georg Lacher Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
Sergey Pashkov Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Georg Lacher Accepted Answer
Hello Sergey,

thank you for your help! Now I'm working on the subform which shows directors, screenwriters, cameramen and actors who were involved in the movie (which is shown in the main form).

Now I'm having a special problem with this subform. The columns don't show the names of the people, but only their indexes in the Personen table. I tried to write a query which shows the names of the people, but this is difficult, because there is more than one foreign key column.

I tried to write subqueries like this:

Select Zuordnung.FNr, Concat (Personen.Name, ', ', Personen.Vorname),
(SELECT Concat(Personen.Name, ',', Personen.Vorname)from Zuordnung Join Personen On Zuordnung.Drehbuch = Personen.RNr)
...
from Zuordnung Join Personen ON Zuordnung.Regie = Personen.RNr where Zuordnung.FNr = $P(P_FNr)

I got an error message: "Subquery returns more than one row".

Do you know how to solve this problem?

Thank you very much
Georg
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
Sergey Pashkov Accepted Answer
Here can be used the same approach with parameters but now the query will be with join.

For example:

1. The list of actors for the movie specified by id:

SELECT * FROM actor
JOIN film_actor ON actor.actor_id = film_actor.actor_id
WHERE film_actor.film_id = $P(pFilmID)


2. The list of movies for the actor specified by id:

SELECT * FROM film
JOIN film_actor ON film.film_id = film_actor.film_id
WHERE film_actor.actor_id = $P(pActorID)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
Sergey Pashkov Accepted Answer
Hello Georg,

Probably with an additional query


Yes, a query should work in this case, I'll add more details soon.

Is it possible to create a form with TWO subforms?


Yes, it is supported
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 6
Georg Lacher Accepted Answer
Thank you, Sergey,

the link to the tutorial helped me a lot.

I created a new form with a subform which shows related records using the third way, using the map of parameters.

The next problem is that my database needs one step more. It has a table with movies and a table with persons and between these tables there is a third table. It shows which persons are related to which films as directors, authors, actors and so on. The third table contains only indexes. How can I manage this problem? Probably with an additional query, but I don't know exactly how.

And another question: Is it possible to create a form with TWO subforms?

Best wishes
Georg
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 7
Sergey Pashkov Accepted Answer
Hello Georg,

Yes, created links (not necessarily using the diagram) can be used to show related records in the subform.

I attached the link to the tutorial which covers this topic.
Also, it is possible to show records in subform even without the link.
References
  1. http://valentina-db.com/docs/dokuwiki/v10/doku.php?id=valentina:products:vstudio:tutorials:forms:lesson2#case_1related_records_using_the_link
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 8
Georg Lacher Accepted Answer
Hello Sergey,

I deleted the selected records. After that there were some other error messages, and I had to delete other lost records. Finally, the changes were applied successfully.

The next question is: what can I do with this diagram? Now Valentina knows how the tables are connected together. Does this help me to go on, for example to create an input form with subforms?

Thank you for you help?
Georg
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 9
Sergey Pashkov Accepted Answer
Hello Georg,

Yes, delete or change FNr to some existing value from the Filme table (to adjust it later).

As usual, it is recommended to create a backup so if something goes wrong you could return to the working copy.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 10
Georg Lacher Accepted Answer
Hello, Sergey.

I found 42 records out of 70.516 records in the Zuordnung table which an unvalid FNr. It's a handmade database, sometimes I discovered double entries in the Filme table and deleted one of them. I tried to adjust the child tables, but obviously sometimes I forgot to do this. So I'm gonna delete these 42 records and try to go on, right?

Thank you!
Georg
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 11
Sergey Pashkov Accepted Answer
Hello Georg,

So there is some data in these tables, right?
Is that possible that some of the FNr values in the Zuordnung table don't exist in the Filme table?

The following query should find such values if any:

SELECT * FROM Zuordnung WHERE Zuordnung.FNr NOT IN ( SELECT FNr FROM Filme )
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 12
Georg Lacher Accepted Answer
I discovered the reason for these errors. Some of the 1:many relations were not correct. I created a new diagram, now everything is ok with the primary key fields.

But when I try to apply the changes, there is a new error message.

The FOREIGN KEY SQL command does not work. I don't know why. I removed the NULLABLE flag from the foreign key field in the child table, but the error comes again.
Attachments (2)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 13
Sergey Pashkov Accepted Answer
Double values are approximate, it may produce unexpected results in some cases, so generally not recommended. But allowed.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 14
Georg Lacher Accepted Answer
Yes, some of the key fields are Double. Is that a problem?
I'm gonna try to discard unnecessary changes.
Thanks!
Georg
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 15
Sergey Pashkov Accepted Answer
Looks like the Unique flag is removed from the PK fields, but why it happens is still the question.

I recommend discarding unnecessary changes.
You can select PK field and use the context menu to discard changes (see screenshot).
The same for `PRIMARY` index and unique.

Also, there are some FK/PK fields that are Double, is that correct?
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 16
Georg Lacher Accepted Answer
Unfortunately the new diagram causes the same error message: "Error (1075): 42000: "Incorrect table definition; there can be only one auto column and it must be defined as a key".

I don't know what's the reason for the error message.

Some fields in the diagram are highlightened in orange colour. Perhaps this is a hint? I checked the links, there is no type mismatch. Maybe some of the 1:many relations are not correct?
Attachments (3)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 17
Sergey Pashkov Accepted Answer
Yes, maybe start on a new diagram, not necessary to drop an old one.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 18
Georg Lacher Accepted Answer
Now I understand ... "auto columns" means fields with AUTO INCREMENT.
The error message means that each auto increment field must also be defined as a key.

I checked the tables in the MySQL database, but each auto increment field is also defined als a primary key field. So why do I get this error message?

Then I found an interesting explanation. When creating a 1:many-link in the diagram Valentina Studio adds a new field as primary key field to the many-table. I deleted these new fields because each table already has a primary key field.

The "apply changes" button creates a script. The script begins with lines like this:
DROP INDEX `PRIMARY` ON `Table Name`;

I assume these lines were added because I deleted these fields. Right?

So the tables do not have a primary key field anymore, and this causes the error message.

What do you suggest? Shall I delete the diagram and create a new one and leave the new fields? Or shall I delete the DROP INDEX lines and then apply the changes?

Thank you for your help
Georg
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 19
Georg Lacher Accepted Answer
Yes, I use Valentina Studio Pro. You're right, the diagram has not been saved properly.
If I try to apply changes, there is an error message: "Error (1075): 42000: "Incorrect table definition; there can be only one auto column and it must be defined as a key".
But I don't know what an "auto column" is.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 20
  • Page :
  • 1
  • 2


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