[Solved] Representing ID's as real values

Creating tables and queries

[Solved] Representing ID's as real values

Postby AndrewRV » Fri Oct 23, 2015 5:19 pm

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
AndrewRV
 
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

Re: Representing ID's as real values

Postby F3K Total » Fri Oct 23, 2015 6:57 pm

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 58 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
F3K Total
Volunteer
 
Posts: 974
Joined: Fri Dec 16, 2011 8:20 pm

Re: Representing ID's as real values

Postby AndrewRV » Fri Oct 23, 2015 11:05 pm

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 59 times
OpenOffice 4.1.1 on Windows Server 2012 R2
AndrewRV
 
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

Re: Representing ID's as real values

Postby F3K Total » Fri Oct 23, 2015 11:23 pm

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

Code: Select all   Expand viewCollapse view
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
F3K Total
Volunteer
 
Posts: 974
Joined: Fri Dec 16, 2011 8:20 pm

Re: Representing ID's as real values

Postby AndrewRV » Sat Oct 24, 2015 9:40 am

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
AndrewRV
 
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm

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

Postby F3K Total » Sat Oct 24, 2015 4:44 pm

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   Expand viewCollapse view
"Genre"."GenreName" AS G1$
does not work
Code: Select all   Expand viewCollapse view
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
F3K Total
Volunteer
 
Posts: 974
Joined: Fri Dec 16, 2011 8:20 pm

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

Postby AndrewRV » Sat Oct 24, 2015 8:27 pm

Got it. Thanks again, you've been a great help :super:
OpenOffice 4.1.1 on Windows Server 2012 R2
AndrewRV
 
Posts: 8
Joined: Fri Oct 23, 2015 4:46 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests