Page 1 of 1

[Solved] LI5:...stuff from two tables???

PostPosted: Wed Oct 03, 2018 5:31 pm
by Kirjava
I got E in GCSE-ICT.

I have two tables.
Table 1: Films & TV
Title, Format, Location

Table 2: Games
Title, Format, Location

I want a list of everything in any particular place.
Example: Shelf 5

I want one list of everything on Shelf 5.

How do I do that?

Re: LI5: How to make one list with stuff from two tables???

PostPosted: Wed Oct 03, 2018 6:32 pm
by Sliderule
Kirjava wrote:I got E in GCSE-ICT.

I have two tables.
Table 1: Films & TV
Title, Format, Location

Table 2: Games
Title, Format, Location

I want a list of everything in any particular place.
Example: Shelf 5

I want one list of everything on Shelf 5.

How do I do that?


Use the following Query:

Code: Select all   Expand viewCollapse view
Select A.* From
(
Select
    "Files & TV"."Title",
    "Files & TV"."Format",
    "Files & TV"."Location"
From "Files & TV"
Where "Files & TV"."Location" = 'Shelf 5'

   UNION

Select
    "Games"."Title",
    "Games"."Format",
    "Games"."Location"
From "Games"
Where "Games"."Location" = 'Shelf 5'
) as A


Alternatively, if you want a Query where the user is prompted for the "Location", use the following Query:

Code: Select all   Expand viewCollapse view
Select A.* From
(
Select
    "Files & TV"."Title",
    "Files & TV"."Format",
    "Files & TV"."Location"
From "Files & TV"
Where "Files & TV"."Location" = :Enter_01_Location

   UNION

Select
    "Games"."Title",
    "Games"."Format",
    "Games"."Location"
From "Games"
Where "Games"."Location" = :Enter_01_Location
) as A


Explanation: By using the SQL UNION statement, it will combine the results as you desire. If you need information about UNION, please do an Internet Search on SQL Tutorial UNION.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: LI5: How to make one list with stuff from two tables???

PostPosted: Wed Oct 03, 2018 6:34 pm
by F3K Total
Hello,
find attached an example, using a query with the SQL-Command
Code: Select all   Expand viewCollapse view
UNION
Then find in there a search form for the location using a one-row-filtertable
R

Re: LI5: How to make one list with stuff from two tables???

PostPosted: Wed Oct 03, 2018 7:23 pm
by Kirjava
I don't know what UNION or SQL are.

Re: LI5: How to make one list with stuff from two tables???

PostPosted: Wed Oct 03, 2018 7:32 pm
by Villeroy
Kirjava wrote:I don't know what UNION or SQL are.

Then you can't do it.

Re: LI5: How to make one list with stuff from two tables???

PostPosted: Wed Oct 03, 2018 7:42 pm
by Kirjava
Forget it I'll just filter the tables.

Re: LI5: How to make one list with stuff from two tables???

PostPosted: Wed Oct 03, 2018 8:42 pm
by Sliderule
Kirjava wrote:I don't know what UNION or SQL are.

That is why, in my first post above, I said:
sliderule wrote:
Sliderule wrote:If you need information about UNION, please do an Internet Search on SQL Tutorial UNION.

Explanation:
  1. Open your OpenOffice / LibreOffice Base file ( *.odb ).

  2. On the left, under Database, click on the Queries icon.

  3. Under Tasks, click on Create Query in SQL View...

  4. Copy and paste, either:
    1. the first Query I wrote above
    2. the second Query I wrote above
  5. Run the Query by either:
    1. Press the Run Query icon ( two sheets with a GREEN check mark ) on the toolbar
    2. Press F5 key
    3. From the Menu: Edit -> Run Query
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.