Querying 3 tables without duplicates

Question

I have 3 tables:
Jobs | messages | Quotes

the jobs table has all information about a posted job. The job_id is auto_increment. The messages table contains a message if one is posted against the jobs job_id.

Quotes table contains any quotes submitted against the jobs job_id.

I want to:

 select all from jobs where ...blah
 select if exists from messages any message where messages table job_id = job table job_id
 select if exists from quotes any quote where quotes job_id = job table job_id

is this possible? i did this left join query, but it gives duplicate results.

    $sql = "SELECT DISTINCT jobs.job_id AS jobid,
    jobs.worktitle AS wrktitle,
    jobs.workinfo AS wrkinfo,
    messages.message AS msg FROM jobs 
    LEFT JOIN messages ON
    jobs.job_id = messages.job_id
    LEFT JOIN quotes ON
    jobs.job_id = quotes.workid";

Please Help! Thanks steve

ok sorry my question wasnt great. iam trying to query my jobs table which is straight forward. However i have 2 other tables associated with this table via a job id column. messages and quotes. The message table contains any questions asked about a particular job via the job id -- The quotes table contains any quotes submitted against a particular job via job id. I have tried to query the jobs table and join the messages and quote tables via job id. but this gives duplicate rows of the jobs table. is it possible to query the jobs table, and show any messages or quotes? – Stevie P

so do you want jobs that have messages and quotes at the same time? — Bulat, Sep 10 '14 at 21:31
it will help if you could post sample data and expected result. consider using SqlFiddle.com and add link to schema with data to your question. — Bulat, Sep 10 '14 at 21:32
Yes i want jobs -- display results and also display together with any messages and any quotes that have the same job id im sorry iam new and dont know how to do the other stuff you asked for — Stevie P, Sep 10 '14 at 21:35
please read [MY POST](http://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056#271056) i wrote about how to ask a good question. it'll help you get better answers — John Ruddell, Sep 10 '14 at 21:38
this might be useful http://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql — Bulat, Sep 10 '14 at 21:40
ok sorry my question wasnt great. iam trying to query my jobs table which is straight forward. However i have 2 other tables associated with this table via a job id column. messages and quotes. The message table contains any questions asked about a particular job via the job id -- The quotes table contains any quotes submitted against a particular job via job id. I have tried to query the jobs table and join the messages and quote tables via job id. but this gives duplicate rows of the jobs table. is it possible to query the jobs table, and show any messages or quotes? — Stevie P, Sep 10 '14 at 21:49
StevieP, it looks much better now, thanks! When you say you don't want duplicate rows, how do you want it to look when a Job has 2 messages? Do you want a count of the messages, or do you only want to see jobs that have 1 or more messages? Edit: Or do you want to show Job A, msg1, msg2, msg3 all on one row? Because the way these joins work you're always going to end up with duplicate rows, unless you agregate some columns, for instance count the number of messages rather then trying to display each one. — BClaydon, Sep 10 '14 at 22:06
I have to wonder why are would you want to do that though. As your data grows, pulling all that data at once will slow down your system. I think you should consider pulling all the jobs only and as a job is selected you can go pull the messages and quotes for that specific job. — Ferox, Sep 10 '14 at 22:07
I added an Edit to my query that addresses the choice of either doing this in the display layer or using Grouping and Aggregates (such as counting and summing) — BClaydon, Sep 10 '14 at 22:23

score 0 · Answer 1 · edited May 23 '17 at 12:29

From OP: "

Job id --- 001

title --- replace window

Details --- blah blah

message -- message 1

quotes - £100

Job id --- 001

title --- replace window

Details --- blah blah

message -- message 2

quotes -- £90

Job id --- 001

title --- replace window

Details --- blah blah

message -- message 3

quotes -- £110

Where as I am after

Job id --- 001 

title  --- replace window

Details --- blah blah

message -- message 1

           message 2

           message 3

quotes --  £100

           £90

           £110

"

Given that the number of quotes and messages can grow, the number of columns or rows of the result set must grow too. SQL generally returns repeating rows, but there is PIVOT function that can add columns. See here: Dynamically create columns sql But in other words, in a one to many relationship (one Job to many quotes) one row will be returned for each record where a job is related to a quote.

However, there nothing sane you can do in SQL to produce the desired results, so you must do it in PHP or whatever your front end is. What I would do would in would be to write the first Job ID, Titles, Details to screen, then loop through the result set adding quotes and messages until a new Job ID is found, and then repeat. Sorry, I do not have a PHP answer.

Querying 3 tables without duplicates

1 Answers1