SQL Query with table joins

Posted on 2014-08-22
Medium Priority
Last Modified: 2014-08-22

What would the SQL query be to select all fields from the table shown in the image.
I used Access to get the image but want to understand how someone would ma manually
write the SQL script to pull data from multiple joined tables.

Question by:Murray Brown
  • 2
  • 2

Accepted Solution

Dukster131 earned 1336 total points
ID: 40279155
First, the mapping for the tables is wrong.  Everything is going to feed into Jobs.  I would assume that you have a ClientID in Jobs.  If you have multiple contacts you may have to have multiple links with the Contacts using aliases for the Contacts table.  Alternatively you could create a juntion table that links Contact_ID to Job ID.  The same thing is true for the materials table.  A junction table would be most appropriate for multiple Materials per job linking Job ID and Materials ID.

Author Comment

by:Murray Brown
ID: 40279189
Hi. Thanks. I am really just looking for an example of 3 or four tables that are all linked together. Jobs does have a ClientID. I just need some direction on linking multiple tables

Assisted Solution

Dukster131 earned 1336 total points
ID: 40279221
Select(whatever you want to select in the tables)
from Jobs j
inner join Clients c on j.ClientID=c.ClientID
inner join Materials m on m.materialsID = j.materialsID
inner join Contacts s on s.Contact_ID = j.Contact_ID

If you are not always getting a Client or contact, then you may have to do outer joins.
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 664 total points
ID: 40279230
As a general example of joining multiple tables into a single query, let's assume we have  tables:

Suppliesr - supplier_no, supplier_name
orders - order_no, supplier_no, order_date
order_items - order_no, order_item_no, stock_item
stock - stock_item, stock_item_name

If you wanted to pull all the details for an order:

select a.order_no, a.supplier_no, a.order_date, b. order_item_no, b.stock_item, d.stock_item_name
from orders a
join order_items b on a.order_no = b.order_no
join suppliers c on a.supplier_no = c.supplier_no
join stock d on b.stock_item = d.stock_item

Open in new window

Start with the base query on the table that drives your query

Add in tables with joins one at a time to build your query.

Author Closing Comment

by:Murray Brown
ID: 40279371
Thanks very much for the help

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question