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

Discuss the database features
Post Reply
Kirjava
Posts: 30
Joined: Sun May 03, 2015 2:58 pm

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

Post 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?
Last edited by Kirjava on Mon Oct 08, 2018 8:01 pm, edited 2 times in total.
OpenOffice 3.1 on Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

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

Post 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

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

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

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

Post by F3K Total »

Hello,
find attached an example, using a query with the SQL-Command

Code: Select all

UNION
Then find in there a search form for the location using a one-row-filtertable
R
Attachments
Two_Tables_List.odb
(28.48 KiB) Downloaded 181 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
Kirjava
Posts: 30
Joined: Sun May 03, 2015 2:58 pm

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

Post by Kirjava »

I don't know what UNION or SQL are.
OpenOffice 3.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

Kirjava wrote:I don't know what UNION or SQL are.
Then you can't do it.
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
Kirjava
Posts: 30
Joined: Sun May 03, 2015 2:58 pm

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

Post by Kirjava »

Forget it I'll just filter the tables.
OpenOffice 3.1 on Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

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

Post 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.
Post Reply