[Solved] SQL - complex one
[Solved] SQL - complex one
hey there friends. Need your help to achieve a query to the dB in order to obtain data.
The starting is, videogames genres.
So for example we have the main genre 'Strategy', and into it we have several sub-genres like for example RTT, RTS, TBS, etc.
So now i want to perform a query in order to fill a form control table grid but taking in consideration that if we go to list games by the genre name 'Stategy' that is the main genre name, i want to list all the titles into the main genre 'Strategy', and all the sub-genres into Strategy. Only on the main one, because when i want to query some of sub-genres, we will want to obtain only that sub-genre game.
In order to help to design the query i have inserted in the table a field with the name 'Main Genre' that is filled in all genres saying 'Stragegy'. That means the Genre "Name" field and the "Main Genre" field only are equal when retrieving 'Strategy' value. It was just an idea to help on all this....
For some reason i am not reaching a good end. I tried with 'inner join' with bad results.
help
The starting is, videogames genres.
So for example we have the main genre 'Strategy', and into it we have several sub-genres like for example RTT, RTS, TBS, etc.
So now i want to perform a query in order to fill a form control table grid but taking in consideration that if we go to list games by the genre name 'Stategy' that is the main genre name, i want to list all the titles into the main genre 'Strategy', and all the sub-genres into Strategy. Only on the main one, because when i want to query some of sub-genres, we will want to obtain only that sub-genre game.
In order to help to design the query i have inserted in the table a field with the name 'Main Genre' that is filled in all genres saying 'Stragegy'. That means the Genre "Name" field and the "Main Genre" field only are equal when retrieving 'Strategy' value. It was just an idea to help on all this....
For some reason i am not reaching a good end. I tried with 'inner join' with bad results.
help
Last edited by Hagar Delest on Fri Nov 24, 2017 9:35 am, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
Re: SQL - complex one
Post your Base document with some sample data. This might help to understand your question
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: SQL - complex one
Ok, i have this:
Like i said, the itention is to fill a tablegrid in a form. When the form is showing 'Strategy' i need to show all the strategy genre games and its sub-genres. But when the form is showing some sub-genre, only the ones that equal that one are showed. I have to use 'like' because a game can have several genres, each one per line. In the table "Genres" each entry for 'Strategy' contains "Name" and "Main Genre". For the case of the entry 'Stragey', "Main Genre" = 'Strategy', the same for the sub-genres. Like i said the idea is to use that to detect when "Name" and "Main Genre" are different.
Taking for example the game 'Age of Empires', it is defined as 'RTS' game that is a sub-genre of the 'Strategy'. I want to obtain two entries on the tablegrid for that game, 1 saying that is 'Strategy' and other saying that is 'RTS'. Then in the form we can filter the tablegrid for 'Strategy' and for 'RTS' when proceed.
My query fails and at the moment i am not understanding why.
Code: Select all
select "Name", "Genre" from "Games"
inner join "Genres" on "Games"."Genre" like '%' || "Genres"."Name" || '%'
and casewhen("Genres"."Name" = "Genres"."Main Genre", "Genres"."Main Genre", "Genres"."Name") = 'Strategy'
order by "Year" asc
Taking for example the game 'Age of Empires', it is defined as 'RTS' game that is a sub-genre of the 'Strategy'. I want to obtain two entries on the tablegrid for that game, 1 saying that is 'Strategy' and other saying that is 'RTS'. Then in the form we can filter the tablegrid for 'Strategy' and for 'RTS' when proceed.
My query fails and at the moment i am not understanding why.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
Re: SQL - complex one
I'm unfortunate not a native speaker, so I do not understand you. I've no idea about your data model and how you fill your data nor it is clear to me how you layout your form. Please attach an example Base document with your form definition and some sample data as requested before and describe your form interaction.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: SQL - complex one
Sounds like "cascading listboxes".
The attachment is free of macros.
The attachment is free of macros.
- Attachments
-
- countries_cities.odb
- Macro free cascading list box and alternative concatenation list box ("AUS Melbourne", "BRA Brasilia")
- (23.27 KiB) Downloaded 171 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: SQL - complex one
i am neither english native, but i think the explanation is good.
Our form 'Genres' has a table grid where i want to process the query. So when the form is showing the record 'Strategy' the table grid must show all the games that have 'Strategy' on the table "Games"."Genre" and all the ones that their genre is a sub-genre of Strategy. Example RTS, TBS, RTT, etc. Because all of them are owned by the main genre called 'Strategy'.
But when the form is showing the record of some sub-genre of the Strategy, the table grid must show only these games that contains "Games"."Genre" = 'RTS' or the others.
So we have two tables involved. "Games" and "Genres".
With the idea to help, by some way to the query, in "Genres" we have a field called "Genres"."Main Genre", that is always filled withe the text 'Strategy', even when the form is showing the record 'Strategy', that is:
"Genres"."Name" = 'Strategy'
"Genres"."Main Genre" = 'Strategy'
When the form is showing the record for 'RTS' we have:
"Genres"."Name" = 'RTS'
"Genres"."Main Genre" = 'Strategy'
And in the "Games" table we have:
"Games"."Genre" = 'RTS'
So with that, we have to perform a sql query that in the case of for example 'Age of Empires', the table grid must show 2 entries, one for 'Strategy' that it is its main genre, and other for 'RTS' that is its specialized genre. We need the sql show the 2 entries because once we filter the table grid, we will obtain the wanted results when we are browsing the "Genres" table.
But like i said, when our form is showing the record for 'Strategy', must show all the games that have as "Main Genre" = 'Strategy', but will show only the games that matches the sub-genres when our form is showing the record for 'RTS'.
For some reasson i am not achieving that sql query. Maybe i am obsesed with the INNER JOIN...
help
Our form 'Genres' has a table grid where i want to process the query. So when the form is showing the record 'Strategy' the table grid must show all the games that have 'Strategy' on the table "Games"."Genre" and all the ones that their genre is a sub-genre of Strategy. Example RTS, TBS, RTT, etc. Because all of them are owned by the main genre called 'Strategy'.
But when the form is showing the record of some sub-genre of the Strategy, the table grid must show only these games that contains "Games"."Genre" = 'RTS' or the others.
So we have two tables involved. "Games" and "Genres".
With the idea to help, by some way to the query, in "Genres" we have a field called "Genres"."Main Genre", that is always filled withe the text 'Strategy', even when the form is showing the record 'Strategy', that is:
"Genres"."Name" = 'Strategy'
"Genres"."Main Genre" = 'Strategy'
When the form is showing the record for 'RTS' we have:
"Genres"."Name" = 'RTS'
"Genres"."Main Genre" = 'Strategy'
And in the "Games" table we have:
"Games"."Genre" = 'RTS'
So with that, we have to perform a sql query that in the case of for example 'Age of Empires', the table grid must show 2 entries, one for 'Strategy' that it is its main genre, and other for 'RTS' that is its specialized genre. We need the sql show the 2 entries because once we filter the table grid, we will obtain the wanted results when we are browsing the "Genres" table.
But like i said, when our form is showing the record for 'Strategy', must show all the games that have as "Main Genre" = 'Strategy', but will show only the games that matches the sub-genres when our form is showing the record for 'RTS'.
For some reasson i am not achieving that sql query. Maybe i am obsesed with the INNER JOIN...
help
Last edited by arfgh on Sun Nov 05, 2017 9:57 pm, edited 1 time in total.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
Re: SQL - complex one
The latter description makes things more clear.
This is indeed complex because your main-form is effectively selecting two alternating different properties and the main-form -- sub-form (grid) binding allows only to have one-to-one bindings. So you have to list all games with two different properties and map that to one field that can be bind.
You can use UNION for that (or UNION ALL) if you have all your games sub-categorized. Sketch of query:
You might need to play with the SQL kind "Direct SQL" vs "Base parsed SQL", for I'm not sure if Base likes UNION in SQL text.
One thing is not clear at the moment, are you using a temporary table to store your filter of your main form. That might be something that can be used as well. Also have a look to Vileroy two-phase selection. That might make the grid-query simpler.
Success.
This is indeed complex because your main-form is effectively selecting two alternating different properties and the main-form -- sub-form (grid) binding allows only to have one-to-one bindings. So you have to list all games with two different properties and map that to one field that can be bind.
You can use UNION for that (or UNION ALL) if you have all your games sub-categorized. Sketch of query:
Code: Select all
select "Games".*
, "Genres"."Name" AS "Select Genre"
from "Games"
inner join "Genres"
on "Genres"."Name" = "Games"."Genre"
UNION
select "Games".*
, "Genres"."Main Genre" AS "Select Genre"
from "Games"
inner join "Genres"
on "Genres"."Name" = "Games"."Genre"
order by "Games"."Title"
One thing is not clear at the moment, are you using a temporary table to store your filter of your main form. That might be something that can be used as well. Also have a look to Vileroy two-phase selection. That might make the grid-query simpler.
Success.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: SQL - complex one
eremmel i will study your example today, but there is another last detail. The games on their "Genre" can have more than one genre. That means for example
that a game can be 'Strategy' and 'Shoot 'em up'. So both definitions are on the field "Games"."Genre", one per line. So then the LIKE operator is needed. Again the sql query must show entries on the table grid for that games, one line per definitions in 'Games"."Genre". Example:
"Game"."Name" = 'Dune'
"Game"."Genre" = 'AdventureStrategy' <-- separed by the character 0, two lines in the form textbox control.
The result for that game in the table grid must be 2 lines for that game. One for 'Adventure' and other for 'Strategy'.
I f the thing keep being complex, i will perform a little sample DB file, just make me know it.
that a game can be 'Strategy' and 'Shoot 'em up'. So both definitions are on the field "Games"."Genre", one per line. So then the LIKE operator is needed. Again the sql query must show entries on the table grid for that games, one line per definitions in 'Games"."Genre". Example:
"Game"."Name" = 'Dune'
"Game"."Genre" = 'AdventureStrategy' <-- separed by the character 0, two lines in the form textbox control.
The result for that game in the table grid must be 2 lines for that game. One for 'Adventure' and other for 'Strategy'.
I f the thing keep being complex, i will perform a little sample DB file, just make me know it.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
Re: SQL - complex one
You still are not telling how you want to make the connection between main-form and sub-form and whether you are using a help-table as a filter table.
Having in a field like Game.Genre multiple genres listed, indicates a bad datamodel design (check out "database normalization", up to 3NF is good enough). You need to have a N-to-M relation between Games and Genres: call that table GameGenres with as primary key two fields PK-of-Games and PK-of-Genres.
Having in a field like Game.Genre multiple genres listed, indicates a bad datamodel design (check out "database normalization", up to 3NF is good enough). You need to have a N-to-M relation between Games and Genres: call that table GameGenres with as primary key two fields PK-of-Games and PK-of-Genres.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: SQL - complex one
The connection between "Genres" main form and its table grid subform is, with your example aliases, the follow:
"Genres"."Name" = "Select Genre"
Then when the "Genres" from is browsing and reach for example 'Strategy', will filter the table grid and will show all the strategy games + all the strategy sub-genres. Etc etc. I havent found a better way to do it. See the fact that the complexity happen using the "Genres" form and its table grid.
And by the way, we need a casewhen for the "Select Genre" or something, because, when "Genres"."Main Genre" = "Genres"."Name" we need to throw "Main Genre" as result. This case only happen when we browse the main genres aka 'Strategy', 'Adventure', 'RPG', 'Racing', etc.
At the moment i am impressed with your example. For the game 'Age of Empires' thow 2 results, Strategy and RTS. But like i said, depending of the "Genre"."Name" whe are browsing, both results must be 'Strategy' for the case when we are on 'Strategy', and 'RTS' when we are browing 'RTS'.
"Genres"."Name" = "Select Genre"
Then when the "Genres" from is browsing and reach for example 'Strategy', will filter the table grid and will show all the strategy games + all the strategy sub-genres. Etc etc. I havent found a better way to do it. See the fact that the complexity happen using the "Genres" form and its table grid.
And by the way, we need a casewhen for the "Select Genre" or something, because, when "Genres"."Main Genre" = "Genres"."Name" we need to throw "Main Genre" as result. This case only happen when we browse the main genres aka 'Strategy', 'Adventure', 'RPG', 'Racing', etc.
At the moment i am impressed with your example. For the game 'Age of Empires' thow 2 results, Strategy and RTS. But like i said, depending of the "Genre"."Name" whe are browsing, both results must be 'Strategy' for the case when we are on 'Strategy', and 'RTS' when we are browing 'RTS'.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
Re: SQL - complex one
Focus first on your data model. Check out many-to-many relationship. It contains also example databases to study.
The idea to work with natural primaries key is a very sensitive to for typos, just use IDs (numbers) as PK and use List Boxes to select the right Foreign Key (FK).
The idea to work with natural primaries key is a very sensitive to for typos, just use IDs (numbers) as PK and use List Boxes to select the right Foreign Key (FK).
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: SQL - complex one
for some reason "Direct SQL" mode on the tablegrid is causing that show all the games instead to filter as must.... why ?
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
Re: SQL - complex one
Look like that Direct SQL does not work in combination main -> sub form binding. I guess that Base extends the sub-form-query with a WHERE clause, so i needs to be able to parse the query, which is not possible with Direct SQL.
When you are using the internal HSQLDB of Base, the only solution I see is that you examine the example of Villeroy "cascading listboxes". The main form is a filter that links to a single record in the table filter and from there you go to the 'real' data via a sub-form.
You can do the same:
1. Create a main-form for filtering Genre and link the Genre details via sub-form and use the grid as an other sub-form.
2. The query for the grid has now access to a single record in the filter-table and the possibility to do nice things, for you are not bounded to the main-sub-form filtering.
3. The grid-query is a smart join from filter -> Genres - Games.
When you use an external database you can put the proposed query in a view definition, but I'm not sure if the complexity of your current design can be solved in this way.
Note: the query is now wrapped in an other query so it looks like Base is smarter than I thought.
When you are using the internal HSQLDB of Base, the only solution I see is that you examine the example of Villeroy "cascading listboxes". The main form is a filter that links to a single record in the table filter and from there you go to the 'real' data via a sub-form.
You can do the same:
1. Create a main-form for filtering Genre and link the Genre details via sub-form and use the grid as an other sub-form.
2. The query for the grid has now access to a single record in the filter-table and the possibility to do nice things, for you are not bounded to the main-sub-form filtering.
3. The grid-query is a smart join from filter -> Genres - Games.
Edit: Received from Sliderule the following tip for the query (It should run as Base SQL): |
Code: Select all
Select A.* From
(
select "Games".*
, "Genres"."Name" AS "Select Genre"
from "Games"
inner join "Genres"
on "Genres"."Name" = "Games"."Genre"
UNION
select "Games".*
, "Genres"."Main Genre" AS "Select Genre"
from "Games"
inner join "Genres"
on "Genres"."Name" = "Games"."Genre"
) as A
order by A."Title"
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: SQL - complex one
eremmel
At finally achieve a good end.
Without direct sql. It works that good and fine, that even threw unexpected but good results.
Filtering that query to a title like for example 'Rise of The Tomb Raider' that contains on its genre 'Action-Adventure', that is a sub-genre of 'Adventure', the result show 3 entriers, one for 'Action-Adventure', other for 'Adventure', and other for 'Action'. A result that overpassed the primary purpose with this, but that is highly good.
I dont know why my end query need no direct sql and your example yes. It is near the same !
Like i said i was obsessed with INNER JOIN because i thought that the table will duplicates entries but it didnt. UNION did that.
Thx so much for your example that showed the way.
At finally achieve a good end.
Code: Select all
select "Year", "Name", "System", "Genre", "Select"
from
(
select "Year", "Title", "System", "Genre", "Genres"."Name" AS "Select"
from "Games"
inner join "Genres"
on "Games"."Genre" like '%' || "Genre"."Name" || '%'
union
select "Year", "Title", "System", "Genre", "Genres"."Main Genre" AS "Select"
from "Games"
inner join "Genres"
on "Games"."Genre" like '%' || "Genres"."Name" || '%'
)
Filtering that query to a title like for example 'Rise of The Tomb Raider' that contains on its genre 'Action-Adventure', that is a sub-genre of 'Adventure', the result show 3 entriers, one for 'Action-Adventure', other for 'Adventure', and other for 'Action'. A result that overpassed the primary purpose with this, but that is highly good.
I dont know why my end query need no direct sql and your example yes. It is near the same !
Like i said i was obsessed with INNER JOIN because i thought that the table will duplicates entries but it didnt. UNION did that.
Thx so much for your example that showed the way.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version