Sql statement join question

[Adamhumbug]

Hi All,

I have a table that contains jobs and another that contains quotes and another that contains clients.

I have a query:

I want to select all jobs for a certain client (that part i can do), i also want to know if these jobs have quotes attached to them.

I have the following:

SELECT distinct job.id, job.name, job.internal_ref, venue, start_date, end_date, quote.quote_status_id, quote.id as quoteId
            from job 
            left join quote on job.id = quote.job_id
            where job.client_id = :clientId

But this will create a row for each job per quote that is attached to it as well as showing all jobs that dont have quotes - so no good.

If i inner join rather than left joining the quote table, i only get rows for jobs with quotes and again another job row per quote.

All i want to know is per job, if there is at least 1 quote attached.

My table structure is.

[Barand]

Something like this? ...
 

SELECT j.id as job_id
     , j.name
     , count(q.id) as quotes
FROM job j
     LEFT JOIN quote q ON j.id = q.job_id
GROUP BY j.id

 

[Adamhumbug]

Thats the one - thank you

Edited September 3, 2023 by Adamhumbug

[Barand]

Do you have any data with j.id = 15? There aren't any in the useless pictures of your data.

[Adamhumbug]

2 hours ago, Barand said:

Do you have any data with j.id = 15? There aren't any in the useless pictures of your data.

Can i ask how you would prefer me to show the content of my databases.

Would it be better for me to give you the create code or is there another way of me showing my data in a way that is more helpful.

I use this forum a lot and i try and take all advice onboard but i dont know what would be preferable when it comes to sharing my table structure.

[Barand]

It's useful if you give us something we can test with. The less time we have to spend on your problem, the more chance there is of a good response. We aren't paid by the hour!

Ideally, a data export dump of the relevant tables with test data so it can be reloaded at our end and the query tested. That way we can see your results and problem first hand.

As with code, pictures of data are as much use as a chocolate teapot.

[Adamhumbug]

59 minutes ago, Barand said:

It's useful if you give us something we can test with. The less time we have to spend on your problem, the more chance there is of a good response. We aren't paid by the hour!

Ideally, a data export dump of the relevant tables with test data so it can be reloaded at our end and the query tested. That way we can see your results and problem first hand.

As with code, pictures of data are as much use as a chocolate teapot.

Ill do my best to provide this going forward.