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

Discuss the database features

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

Postby Kirjava » Wed Oct 03, 2018 5:31 pm

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
Kirjava
 
Posts: 20
Joined: Sun May 03, 2015 2:58 pm

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

Postby Sliderule » Wed Oct 03, 2018 6:32 pm

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.
User avatar
Sliderule
Volunteer
 
Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

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

Postby F3K Total » Wed Oct 03, 2018 6:34 pm

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
Attachments
Two_Tables_List.odb
(28.48 KiB) Downloaded 52 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: 939
Joined: Fri Dec 16, 2011 8:20 pm

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

Postby Kirjava » Wed Oct 03, 2018 7:23 pm

I don't know what UNION or SQL are.
OpenOffice 3.1 on Windows 7
Kirjava
 
Posts: 20
Joined: Sun May 03, 2015 2:58 pm

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

Postby Villeroy » Wed Oct 03, 2018 7:32 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27390
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Kirjava » Wed Oct 03, 2018 7:42 pm

Forget it I'll just filter the tables.
OpenOffice 3.1 on Windows 7
Kirjava
 
Posts: 20
Joined: Sun May 03, 2015 2:58 pm

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

Postby Sliderule » Wed Oct 03, 2018 8:42 pm

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.
User avatar
Sliderule
Volunteer
 
Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am


Return to Base

Who is online

Users browsing this forum: MSN [Bot] and 2 guests