MySQL right outer join query

debugcn Published at Dev

Praba

I have a query regarding a query in MySQL.

I have 2 tables one containing SalesRep details like name, email, etc. I have another table with the sales data which has reportDate, customers served and link to the salesrep via a foreign key. One thing to note is that the reportDate is always a friday.

So the requirement is this: I need to find sales data for a 13 week period for a given list of sales reps - with 0 as customers served if on a particular friday there is no data. The query result is consumed by a Java application which relies on the 13 rows of data per sales rep.

I have created a table with all the Friday dates populated and wrote a outer join like below:

select * from (
         select name, customersServed, reportDate 
         from Sales_Data salesData
         join `SALES_REPRESENTATIVE` salesRep on salesRep.`employeeId` = salesData.`employeeId`
         where employeeId = 1   
    ) as result 
    right outer join fridays on fridays.datefield = reportDate
    where fridays.datefield between '2014-10-01' and '2014-12-31'
   order by datefield

enter image description here

Now my doubts:

Is there any way where i can get the name to be populated for all 13 rows in the above query?
If there are 2 sales reps, I'd like to use a IN clause and expect 26 rows in total - 13 rows per sales person (even if there is no record for that person, I'd still like to see 13 rows of nulls), and 39 for 3 sales reps

Can these be done in MySql and if so, can anyone point me in the right direction?

syllabus

You must first select your lines (without customersServed) and then make an outer join for the customerServed

something like that:

select records.name, records.datefield, IFNULL(salesRep.customersServed,0)
from (
         select employeeId, name, datefield 
         from `SALES_REPRESENTATIVE`, fridays
         where fridays.datefield between '2014-10-01' and '2014-12-31'
         and employeeId in (...)
    ) as records 
    left outer join `Sales_Data` salesData on (salesData.employeeId = records.employeeId and salesData.reportDate = records.datefield)
   order by records.name, records.datefield

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-2

Comments

0 comments

From Dev

Related Related

Article

MySQL right outer join query

MySQL right outer join query

Right outer join in linq to entities query

MySQL - endless running query on LEFT OUTER JOIN

MySQL query, three tables, outer join

HQL right outer join

Right Outer Join to Left Outer join

full outer join or right outer join

MySQL - how do I do an outer join where either side is optional - left and right outer join

MySQL Right Outer Join Null Value, Standings(Rankings) With All Users

Select MAX and RIGHT OUTER JOIN

LINQ to SQL Right Outer Join

Linq to object Right outer join

LINQ to SQL Right Outer Join

Is it a good practice to JOIN a UNION result in outer query in MySQL

MySQL query: use LIMIT on one table column having an OUTER JOIN

OUTER JOIN equivalent in MySQL

MySQL outer join substitute

How do you use right outer join to join three separate columns together in mysql?

MySQL - Right join OR Right join

how to write combination of RIGHT OUTER JOIN and LEFT OUTER JOIN

Left Outer join in Ebean query

outer join in a query builder with doctrine

Left Outer Join in sql query

Hibernate Named Query Outer Join

Left Outer join in Ebean query

Outer Join SQL on having query

Right Join / Group by query

How to do full right outer join in kdb?

Perform right outer join with a condition for left table

Right outer join with multiple where conditions