how do i solve that issue?
Errormessage: Incorrect Submission Check the highlighted code. Could not find the second entry in the fields.
SELECT e.FirstName || ' ' || e.LastName AS "Employee",
c.FirstName || ' ' || c.LastName AS "Customer",
sum(i) AS "Total"
-- Join Employee with Customer
FROM Employee e CROSS JOIN Customer c
-- Join with Invoice
CROSS JOIN Invoice i on c.customerid = i.customerid
-- Filter for support agents only
WHERE e.Title = 'Sales Support Agent'
GROUP BY e.FirstName, e.LastName, c.FirstName, c.LastName
CodePudding user response：
cross join doesn't have on statement so,bring your on clause to where like :
where .... and c.customerid = i.customerid
CodePudding user response：
Solution:
-- Select first and last names
SELECT e.FirstName || ' ' || e.LastName AS "Employee",
c.FirstName || ' ' || c.LastName AS "Customer",
sum(i.total) AS "Total"
-- Join Employee with Customer
FROM Employee e CROSS JOIN Customer c
-- Join with Invoice
Inner join Invoice i ON c.customerid = i.customerid
-- Filter for support agents only
WHERE e.Title = 'Sales Support Agent'
GROUP BY e.FirstName, e.LastName, c.FirstName, c.LastName