[Solved] Representing ID's as real values

Creating tables and queries
Post Reply
AndrewRV
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

[Solved] Representing ID's as real values

Post by AndrewRV »

Hi everyone. Can you please help me create a correct view for my tables?

I have a table "Movies", that have the following columns among other: "GenreID1", "GenreID2" and "GenreID3"
And I have another table called "Genres", which looks like this:

0 | Action
1 | Drama
2 | Comedy
e.t.c.

In the "Movies" I use those ID's to represent up to 3 genres from the table "Genre".
Now, I want to create a view that would show me the "Movies" table with actual Genre name instead of ID in all 3 columns.

What query should I use?
And, since I'm here, maybe you know a better way to store and display multiple genres for a movie?
Would be very grateful for help.
Last edited by AndrewRV on Sat Oct 24, 2015 9:46 am, edited 1 time in total.
OpenOffice 4.1.1 on Windows Server 2012 R2
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Representing ID's as real values

Post by F3K Total »

Hello,
AndrewRV wrote:And, since I'm here, maybe you know a better way to store and display multiple genres for a movie?
maybe you like to use a M:N relation, see attached example to investigate.
The listbox in Gridcontrol for Genres is the clou, shows text but writes integer.
See listbox-properties/tab data-> SQL ...
You can relate as much genres to a movie, as you like.
R
Attachments
MOVIES.odb
(11.95 KiB) Downloaded 128 times
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
AndrewRV
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

Re: Representing ID's as real values

Post by AndrewRV »

F3K Total
Thanks for the suggestion. It's a viable option, but as far as I understand, that would make input of new entries through the form very hard

Well, here's a small example of my table for clear understanding. I use a form to easily make new entries. What I need is to be able to create a view/report/form with the data from the table, where instead of GenreID there would be actual genre. Same with Rating column.
Attachments
MoviesExample.odb
(14.64 KiB) Downloaded 137 times
OpenOffice 4.1.1 on Windows Server 2012 R2
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Representing ID's as real values

Post by F3K Total »

Try this:
Queries/Create Query in SQL View...
Paste the following code and save it.

Code: Select all

SELECT 
    "MovieID",
    "MovieName",
    "AltName",
    "Genre"."GenreName" AS "G1",
    "Genre_1"."GenreName" AS "G2",
    "Genre_2"."GenreName" AS "G3",
    "ReleaseYear",
    "ImdbRating",
    "RatingName"
FROM 
    "MoviesCompleted" 
    left join "Genre" ON "MoviesCompleted"."GenreID1" = "Genre"."GenreID" 
    left join "Genre" AS "Genre_1" ON "MoviesCompleted"."GenreID2" = "Genre_1"."GenreID" 
    left join "Genre" AS "Genre_2" ON "MoviesCompleted"."GenreID3" = "Genre_2"."GenreID"
    left join "Rating" ON "MoviesCompleted"."RatingID" = "Rating"."RatingID"
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
AndrewRV
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

Re: Representing ID's as real values

Post by AndrewRV »

F3K Total

Yes! Yes, that's exactly what I wanted! Thank you indeed!
I would never figure out that you could append "_1" "_2" e.t.c. to table names in this case.

B.t.w., can I ask you please why did you use double quotes around everything? It works without them too, so I wonder when is the good time to use them, for further reference.

Again, thank you!
OpenOffice 4.1.1 on Windows Server 2012 R2
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: [Solved] Representing ID's as real values

Post by F3K Total »

Hi,
the underlaying database, HSQL 1.8.10 does not understand Genre but "Genre". You can see that, if you run the SQL command directly.
In your case, it works without double quotes, because OpenOffice has a parser, translating it correct.
Try e.g. a columnname containing a special character, not in direct SQL-Mode, it will not work without double quotes.

Code: Select all

"Genre"."GenreName" AS G1$
does not work

Code: Select all

Genre.GenreName AS "G1$"
works
Conclusions:
Direct SQL-Mode: Only uppercase characters having no special characters work without doublequotes
not direct SQL-Mode: upper and lowercase characters work without doublequotes, if no special character is used.
That's the reason for many people to only use UPPERCASE characters for tablenames, columnames and aliases, they don't need doublequotes at all.
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
AndrewRV
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

Re: [Solved] Representing ID's as real values

Post by AndrewRV »

Got it. Thanks again, you've been a great help :super:
OpenOffice 4.1.1 on Windows Server 2012 R2
Post Reply