[Solved] Selecting Information from Multiple Tables

Discuss the database features
Post Reply
jsvanc
Posts: 10
Joined: Fri Feb 16, 2018 4:11 am

[Solved] Selecting Information from Multiple Tables

Post by jsvanc »

I am trying to build a query by selecting information from 4 different tables. I have tried numerous select statements with JOINS and OUTER JOINS, Sub Queries, etc. I just can't seem to get it right.

Basically what I have is 4 tables, Submittal, ContactCompany, Contacts, JobContacts.

I am trying to make a form that will display my submittal information from the query. I know I'm missing something or I don't have something setup on my tables correctly, but when I run my query, it will show multiple lines with the same information. I know where the lines come from (multiple contacts on the same job), but I need to just display only one line.

What i want to display is this:
Company Name | Submittal Number (If available. Some don't have a number) | Submitted Item | Date Submitted | Response Received | SubmitStatus

Any help on my Query would be GREATLY Appreciated.

I have attached a screenshot to show you what I have right now as far as tables go. The Query was just the last query I put in but even that shows the multiple lines.

Thank you.
Attachments
query_tables.jpg
Last edited by jsvanc on Fri Feb 23, 2018 5:07 am, edited 1 time in total.
OpenOffice 4.1.5 on Windows 10
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Selecting Information from Multiple Tables

Post by Hagar Delest »

No screenshot attached. Make sure it's smaller than 128kbi. Else, use a file sharing web site.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
jsvanc
Posts: 10
Joined: Fri Feb 16, 2018 4:11 am

Re: Selecting Information from Multiple Tables

Post by jsvanc »

Sorry. Screenshot added.

OK. So I got this to select the correct lines, but when I go to the form with this query, I shows the same data on every job.

Code: Select all

SELECT DISTINCT "Submittal".*, "ContactCompany"."CompanyName", "ContactCompany"."ContactType"
FROM "Submittal"
LEFT OUTER JOIN "Jobs" ON "Jobs"."JobID" = "Submittal"."JobID"
LEFT OUTER JOIN "JobContacts" ON "Jobs"."JobID" = "JobContacts"."JobID"
LEFT OUTER JOIN "ContactCompany" ON "Submittal"."CompanyID" = "ContactCompany"."CompanyID"
OpenOffice 4.1.5 on Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Selecting Information from Multiple Tables

Post by UnklDonald418 »

I'm assuming you the form you mention here is the Jobs form document from the database you uploaded to
Dropdown List on Form but don't want that data saved
It seems to work with

Code: Select all

SELECT "Submittal".*, "Jobs".*, "ContactCompany"."CompanyName", "ContactCompany"."ContactType", "Contacts"."ContactName" 
FROM "Submittal", "Jobs", "Contacts", "ContactCompany" 
WHERE "Submittal"."JobID" = "Jobs"."JobID" AND "Submittal"."JobID" = "Submittal"."JobID" AND "Contacts"."CompanyID" = "Submittal"."CompanyID" AND "ContactCompany"."CompanyID" = "Contacts"."CompanyID";
I noticed another issue with your form Jobs that causes problems when navigating on your table controls.
Open both the Properties Form dialog for both subforms and change the Navigation Bar property to Parent Form. That way if you select something on one of the subforms the main navigation controls will remain connected to MainForm.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
jsvanc
Posts: 10
Joined: Fri Feb 16, 2018 4:11 am

Re: [SOLVED] Selecting Information from Multiple Tables

Post by jsvanc »

Thank you @UnklDonald418. Pretty much have everything working. Now I'm having a problem with Dialogs but I will post a new thread for that.
OpenOffice 4.1.5 on Windows 10
Post Reply