This topic is locked

Many to many SQL statement

7/29/2022 1:44:09 PM
PHPRunner General questions
J
Jon_AK author

I have 3 tables: contractors table with PK contractor_id, jobs table with PK job_id & a junction table jobs_contractors with PK contractor_id & job_id. (probably should have named these a bit diffferent due to the ambiguity warning/error I received). For the master table, I have show single link for details selected. When viewing the list of the jobs table, the details for the contractors show up fine in the single link but not showing the contractors name, just the record number... (typical for the junction table). In an attempt to show the contractors name in the detail link, I added left outer joins, but the list wound up showing individual rows for each contractor that is bidding the same job , which is not what I am striving for. Are the joins supposed to go in the SQL code for the junction table? Below is the SQL code I'm up to. How to change this to show the contractors name when opening the details link??

SQL for the jobs table


SELECT
jobs.job_id,
jobs.job_name,
jobs.bid_date,
jobs.bid_time,
jobs.job_walk_date,
jobs.job_walk_time,
jobs.job_city,
jobs.job_state,
jobs.bid_amount
--.contractors.company_name,
--jobs_contractors.job_id AS job_id1
FROM jobs;
--LEFT OUTER JOIN jobs_contractors ON jobs.job_id = jobs_contractors.job_id
--LEFT OUTER JOIN contractors ON jobs_contractors.contractor_id = contractors.contractor_id

Sergey Kornilov admin 7/29/2022

JOIN SQL queries may multiple the number of records returned, based on how the tables are linked and also what kind of data you have.

The solution for you can be a subquery, that would return the contractors name based on this ID. Check "SELECT clause" section in this article.

J
Jon_AK author 7/30/2022

Will give this a whirl. New to using PHPRunner, am figuring out where pieces & parts all fit, not 100% sure if placing the SQL code is to go in the Query section for the master table or if it is supposed to be an event. Thanks for the tip though.

Sergey Kornilov admin 7/30/2022

I think you need to modify it on a SQL screen.