[Solved] SQL - complex one

Discuss the database features
Post Reply
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

[Solved] SQL - complex one

Post by arfgh »

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
Last edited by Hagar Delest on Fri Nov 24, 2017 9:35 am, edited 1 time in total.
Reason: tagged solved
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: SQL - complex one

Post by eremmel »

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)
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL - complex one

Post by arfgh »

Ok, i have this:

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
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.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: SQL - complex one

Post by eremmel »

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)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL - complex one

Post by Villeroy »

Sounds like "cascading listboxes".
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
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL - complex one

Post by arfgh »

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
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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: SQL - complex one

Post by eremmel »

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:

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"
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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL - complex one

Post by arfgh »

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.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: SQL - complex one

Post by eremmel »

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL - complex one

Post by arfgh »

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'.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: SQL - complex one

Post by eremmel »

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).
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL - complex one

Post by arfgh »

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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: SQL - complex one

Post by eremmel »

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.
 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"
Note: the query is now wrapped in an other query so it looks like Base is smarter than I thought.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL - complex one

Post by arfgh »

eremmel

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" || '%'
	)
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.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
Post Reply