CODEWITHSUNDEEP
sqlsql-serverjoinselect
mjordan
mjordan

Reputation: 369

SQL Server : query join with multiple tables and Group By

I would like to join the following three SQL Server tables as shown. I know they have to be JOINed, but I think it is the GROUP BY that is throwing me off. I think it requires a SELECT within a SELECT?

select ActJobs.ID as JobID, ActJobs.JobName, ActJobs.CustomerID
From Job.ActiveJobsTbl as ActJobs

select Cus.ID as CustomerID, concat(Cus.LastName, ', ', Cus.FirstName) as Customer
From Customer.CustomerTbl as Cus

Select Est.ID_Job as JobID, Sum(Est.ItemAmount + Est.SalesTax) As Price
From Job.EstimateTbl as Est 
Group By Est.ID_Job

enter image description here

enter image description here

Upvotes: 0

Views: 40

Answers (2)

GMB
GMB

Reputation: 222462

You can simply JOIN the first two tables, and turn the aggregated query to a subquery that you can also JOIN:

SELECT 
    ActJobs.ID as JobID, 
    ActJobs.JobName, 
    ActJobs.CustomerID,
    CONCAT(Cus.LastName, ', ', Cus.FirstName) as Customer,
    Est.Price
FROM 
    Job.ActiveJobsTbl AS ActJobs
    INNER JOIN Customer.CustomerTbl AS Cus ON Cus.ID = ActJobs.CustomerID
    INNER JOIN (
        SELECT ID_Job, SUM(ItemAmount + SalesTax) As Price 
        FROM Job.EstimateTbl
        GROUP BY ID_Job
    ) AS Est ON Est.ID_Job = ActJobs.ID

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Use scalar subquery and join

select ActJobs.ID as JobID, ActJobs.JobName, ActJobs.CustomerID,
concat(Cus.LastName,', ',Cus.FirstName) as Customer,(select Sum(Est.ItemAmount + Est.SalesTax) from Job.EstimateTbl as Est where Est.ID_Job=ActJobs.ID) as price
From Job.ActiveJobsTbl as ActJobs
inner join Customer.CustomerTbl as Cus on Cus.ID as CustomerID=ActJobs.CustomerID

Upvotes: 0

Related Questions