joining tables

[plezzy]

Hello, I am very new to both php and sql.

I have 3 tables in a database

 

jobs, messages, and quotes

 

What is the best way to select all from jobs WHERE county=$cnty And category=$cat

Then select All from messages WHERE job_id = job_id from jobs table (if there is any)

Then select all from quotes   WHERE job_id = job_id from jobs table (if there is any)?

 

Im guessing that i would need to join the tables? If so can i join tables even if there is no records in one or two of the three tables?

Please, any help would be great.

 

i tried

 

<?php

if ($cnty=="All" && $cat=="All") {$sql = ("SELECT jobs.*,DATE_FORMAT(date, '%d-%m-%Y') as date,messages.*,DATE_FORMAT(date, '%d-%m-%Y') as date, quotes.* ,DATE_FORMAT(date, '%d-%m-%Y') as date

FROM jobs

    JOIN messages

        ON messages.job_id = jobs.job_id

    JOIN quotes

        ON quotes.job_id = messages.job_id ORDER BY job_id DESC LIMIT $start, $limit") or die(mysql_error());}    

elseif 

($cnty!="All" && $cat=="All") {$sql = ("SELECT jobs.*,DATE_FORMAT(date, '%d-%m-%Y') as date,messages.*,DATE_FORMAT(date, '%d-%m-%Y') as date, quotes.* ,DATE_FORMAT(date, '%d-%m-%Y') as date

FROM jobs

    JOIN messages

        ON messages.job_id = jobs.job_id

    JOIN quotes

        ON quotes.job_id = messages.job_id

    WHERE county=$cnty ORDER BY job_id DESC LIMIT $start, $limit") or die(mysql_error());}

elseif ($cnty=="All" && $category!="All") {$sql = ("SELECT jobs.*,DATE_FORMAT(date, '%d-%m-%Y') as date,messages.*,DATE_FORMAT(date, '%d-%m-%Y') as date, quotes.* ,DATE_FORMAT(date, '%d-%m-%Y') as date

FROM jobs

    JOIN messages

        ON messages.job_id = jobs.job_id

    JOIN quotes

        ON quotes.job_id = messages.job_id

    WHERE category=$cat ORDER BY job_id DESC LIMIT $start, $limit") or die(mysql_error());}

else{$sql = ("SELECT jobs.*,DATE_FORMAT(date, '%d-%m-%Y') as date,messages.*,DATE_FORMAT(date, '%d-%m-%Y') as date, quotes.* ,DATE_FORMAT(date, '%d-%m-%Y') as date

FROM jobs

    JOIN messages

        ON messages.job_id = jobs.job_id

    JOIN quotes

        ON quotes.job_id = messages.job_id

    WHERE county=$cnty AND category=$cat ORDER BY job_id DESC LIMIT $start, $limit") or die(mysql_error());}  ?>

 

but dont know how to echo the results or if it works

Edited September 6, 2014 by mac_gyver
code tags please

[jazzman1]

you need to use only 1 query, then use php to build your query with or not where condition depends on the logic you want to apply.

[plezzy]

you need to use only 1 query, then use php to build your query with or not where condition depends on the logic you want to apply.

 

would it be possible to say what kind of join to use... or anything that may assist me?

[mac_gyver]

if there are no messages or no quotes, your straight JOIN won't return any result. to always get the job information, you would use a LEFT JOIN between the jobs table and the other tables.

 

you are also joining quotes to messages.  what your query is doing - jobs have messages, messages have quotes. is that the correct relationship?

 

what jazzman is suggesting is to only write out code for things that are different. that part of the query that's the same should only exist once in your code. the only conditional logic should be the part that building the WHERE clause. this will reduce that amount of clutter and make it easier to see what your code is actually doing.

 

next, having just the necessary code, so that you can see what your code is actually doing, would perhaps let you see that you are using mysql_error() on the end of a php string assignment statement, where it won't do any good, and that you are not actually running the sql query you are building.

[plezzy]

if there are no messages or no quotes, your straight JOIN won't return any result. to always get the job information, you would use a LEFT JOIN between the jobs table and the other tables.

 

you are also joining quotes to messages.  what your query is doing - jobs have messages, messages have quotes. is that the correct relationship?

 

what jazzman is suggesting is to only write out code for things that are different. that part of the query that's the same should only exist once in your code. the only conditional logic should be the part that building the WHERE clause. this will reduce that amount of clutter and make it easier to see what your code is actually doing.

 

next, having just the necessary code, so that you can see what your code is actually doing, would perhaps let you see that you are using mysql_error() on the end of a php string assignment statement, where it won't do any good, and that you are not actually running the sql query you are building.

Thank you for some proper advice. My problem is that jobs = some job ids  have messages associated via messages table(job id's)  and  job ids  have quotes associated via quotes table(job id's)

So are you saying that using a LEFT JOIN will return results even if no records are present from some tables?

[Ch0cu3r]

 

 

So are you saying that using a LEFT JOIN will return results even if no records are present from some tables?

Yes. Have read of this tutorial on the difference between the different types of joins

Edited September 6, 2014 by Ch0cu3r

[plezzy]

Yes. Have read of this tutorial on the difference between the different types of joins

 Thank you... but the link does not work.

[mac_gyver]

if the relationship is between jobs and quotes and jobs and messages and there is no relationship between quotes and messages, then you actually need two queries (unless the columns you want to retrieve from quotes and messages happen to correspond exactly so that you could use a UNION query.)

[Ch0cu3r]

 Thank you... but the link does not work.

Link has been fixed.

[plezzy]

if the relationship is between jobs and quotes and jobs and messages and there is no relationship between quotes and messages, then you actually need two queries (unless the columns you want to retrieve from quotes and messages happen to correspond exactly so that you could use a UNION query.)

yes. the jobs table as all the details and job id is auto increment

messages table - this contains any questions asked about the job attaching via job id

quotes table contains any quotes recieved against the job again via job id

 

so in my table i wanted to show all the jobs where whatever including any messages and any quotes

[mac_gyver]

that would be two separate queries or a carefully crafted UNION of two LEFT JOIN queries.

[plezzy]

that would be two separate queries or a carefully crafted UNION of two LEFT JOIN queries.

How can i stop the following returning duplicate rows?

 

$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";

 

[mac_gyver]

given that you are using the DISTINCT keyword, i doubt there are exact duplicate rows. i suspect you mean that the job information exists in each set of rows for any job_id. if that's the case, that's how joined queries work. if that's not the case, you would need to post the result you are getting.

 

however, that query is not what was suggested. you either need to separate queries, one for jobs and messages and the second for jobs and quotes OR if as has already been stated, if the columns you are selecting for messages and quotes are similar in meaning (because the UNION query will use the column names from the first query in the UNION), you can write one query that combines these two queries as a UNION query.

[plezzy]

given that you are using the DISTINCT keyword, i doubt there are exact duplicate rows. i suspect you mean that the job information exists in each set of rows for any job_id. if that's the case, that's how joined queries work. if that's not the case, you would need to post the result you are getting.

 

however, that query is not what was suggested. you either need to separate queries, one for jobs and messages and the second for jobs and quotes OR if as has already been stated, if the columns you are selecting for messages and quotes are similar in meaning (because the UNION query will use the column names from the first query in the UNION), you can write one query that combines these two queries as a UNION query.

Thanks for the help. I appreciate your advice.  im struggling to get my head around such a query. can you give a quick example of that style of query?.

Im looking to show all  from jobs table.

show if exists message from messages table.

show if exists quote from quotes table

then display with no duplicates.

 

thanks

[jazzman1]

You could do this using a sql case operator to take all possibilities, or use php to build this query.  What have you done so far?