Solved

SQL - Cross joins

Posted on 2014-11-17
7
119 Views
Last Modified: 2014-11-18
This query works but when I try and add another Cross Join it hangs.

SELECT
j.JOBID 'Job',
j.DSCR 'Job Name',
ex.ExtraID 'Extra',
c.CostCode 'Cost Code',
e.EMPID 'Emp ID',
CONCAT(e.EFIRST, ' ', e.EMIDDLE, ' ', e.ELAST) 'Name',
l.HOURS 'Monday',
Tuesday
FROM ccode c
JOIN labor l ON (l.CCODE_ID = c.CCODE_ID)
JOIN job j ON (l.JOB_ID = j.JOB_ID)
JOIN emp e ON (e.EMP_ID = l.EMP_ID)
LEFT JOIN extra ex ON (ex.JOB_ID = j.JOB_ID AND ex.EXTRA_ID = c.EXTRA_ID)
CROSS JOIN (
           SELECT SUM(l.HOURS) Tuesday
           FROM labor l
           LEFT JOIN emp e ON (l.EMP_ID = e.EMP_ID)
           WHERE DATE_WORK = '2014-11-04'
           GROUP BY e.EMP_ID
          ) t
WHERE l.DATE_WORK = '2014-11-03'
AND l.DELETED = 'N'
GROUP BY e.EMP_ID
ORDER BY j.JobID;

This is what I'm tyring to join:

CROSS JOIN (
           SELECT SUM(l.HOURS) Wednesday
           FROM labor l
           LEFT JOIN emp e ON (l.EMP_ID = e.EMP_ID)
           WHERE DATE_WORK = '2014-11-05'
           GROUP BY e.EMP_ID
          ) w         

so the final query is:

SELECT
j.JOBID 'Job',
j.DSCR 'Job Name',
ex.ExtraID 'Extra',
c.CostCode 'Cost Code',
e.EMPID 'Emp ID',
CONCAT(e.EFIRST, ' ', e.EMIDDLE, ' ', e.ELAST) 'Name',
l.HOURS 'Monday',
Tuesday,
Wednesday
FROM ccode c
JOIN labor l ON (l.CCODE_ID = c.CCODE_ID)
JOIN job j ON (l.JOB_ID = j.JOB_ID)
JOIN emp e ON (e.EMP_ID = l.EMP_ID)
LEFT JOIN extra ex ON (ex.JOB_ID = j.JOB_ID AND ex.EXTRA_ID = c.EXTRA_ID)
CROSS JOIN (
           SELECT SUM(l.HOURS) Tuesday
           FROM labor l
           LEFT JOIN emp e ON (l.EMP_ID = e.EMP_ID)
           WHERE DATE_WORK = '2014-11-04'
           GROUP BY e.EMP_ID
          ) t
CROSS JOIN (
           SELECT SUM(l.HOURS) Wednesday
           FROM labor l
           LEFT JOIN emp e ON (l.EMP_ID = e.EMP_ID)
           WHERE DATE_WORK = '2014-11-05'
           GROUP BY e.EMP_ID
          ) w         
WHERE l.DATE_WORK = '2014-11-03'
AND l.DELETED = 'N'
GROUP BY e.EMP_ID
ORDER BY j.JobID;
0
Comment
Question by:hdcowboyaz
  • 4
  • 3
7 Comments
 
LVL 14

Expert Comment

by:quizwedge
Comment Utility
My guess is that it comes down to resources and the amount of data you're pulling. Do you have indexes set up?
0
 

Author Comment

by:hdcowboyaz
Comment Utility
I have some however I can add one extra row and runs in .2 sec but when I add another row it takes minutes. While each query alone is instantaneous.

I've actually added another question in liew of this one.
0
 

Author Comment

by:hdcowboyaz
Comment Utility
Cross Joins - SQL
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 14

Expert Comment

by:quizwedge
Comment Utility
I don't know if this will improve performance and I didn't test it, but you could try the following. Let me know if you have questions about the query.

SELECT 
j.JOBID 'Job', 
j.DSCR 'Job Name', 
ex.ExtraID 'Extra',
c.CostCode 'Cost Code',
e.EMPID 'Emp ID',
CONCAT(e.EFIRST, ' ', e.EMIDDLE, ' ', e.ELAST) 'Name', 
IsNull(select sum(l.HOURS) from labor l where l.C=CCODE_ID = cCCODE_ID and l.EMP_ID = e.EMP_ID and l.DELETED = 'N' and DATE_WORK = '2014-11-03' group by l.HOURS, 0) 'Monday',
IsNull(select sum(l.HOURS) from labor l where l.C=CCODE_ID = cCCODE_ID and l.EMP_ID = e.EMP_ID and l.DELETED = 'N' and DATE_WORK = '2014-11-04' group by l.HOURS, 0) 'Tuesday',
IsNull(select sum(l.HOURS) from labor l where l.C=CCODE_ID = cCCODE_ID and l.EMP_ID = e.EMP_ID and l.DELETED = 'N' and DATE_WORK = '2014-11-05' group by l.HOURS, 0) 'Wednesday',
IsNull(select sum(l.HOURS) from labor l where l.C=CCODE_ID = cCCODE_ID and l.EMP_ID = e.EMP_ID and l.DELETED = 'N' and DATE_WORK = '2014-11-06' group by l.HOURS, 0) 'Thursday',
IsNull(select sum(l.HOURS) from labor l where l.C=CCODE_ID = cCCODE_ID and l.EMP_ID = e.EMP_ID and l.DELETED = 'N' and DATE_WORK = '2014-11-07' group by l.HOURS, 0) 'Friday'
FROM ccode c
JOIN job j ON (l.JOB_ID = j.JOB_ID)
JOIN emp e ON (e.EMP_ID = l.EMP_ID)
LEFT JOIN extra ex ON (ex.JOB_ID = j.JOB_ID AND ex.EXTRA_ID = c.EXTRA_ID)
GROUP BY e.EMP_ID
ORDER BY j.JobID;

Open in new window

0
 

Author Comment

by:hdcowboyaz
Comment Utility
1 queries executed, 0 success, 1 errors, 0 warnings

Query: SELECT j.JOBID 'Job', j.DSCR 'Job Name', ex.ExtraID 'Extra', c.CostCode 'Cost Code', e.EMPID 'Emp ID', CONCAT(e.EFIRST, ' ', e.E...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select sum(l.HOURS) from labor l where l.C=CCODE_ID = cCCODE_ID and l.EMP_ID = e' at line 8
0
 

Author Comment

by:hdcowboyaz
Comment Utility
after fixing some typos...

1 queries executed, 0 success, 1 errors, 0 warnings

Query: SELECT j.JOBID 'Job', j.DSCR 'Job Name', ex.ExtraID 'Extra', c.CostCode 'Cost Code', e.EMPID 'Emp ID', CONCAT(e.EFIRST, ' ', e.E...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select sum(l.HOURS) from labor l where l.CCODE_ID = c.CCODE_ID and l.EMP_ID = e.' at line 8
0
 
LVL 14

Accepted Solution

by:
quizwedge earned 500 total points
Comment Utility
Whoops, wrap the select statements that are inside the IsNull in parentheses. So, something like

SELECT 
j.JOBID 'Job', 
j.DSCR 'Job Name', 
ex.ExtraID 'Extra',
c.CostCode 'Cost Code',
e.EMPID 'Emp ID',
CONCAT(e.EFIRST, ' ', e.EMIDDLE, ' ', e.ELAST) 'Name', 
IsNull((select sum(l.HOURS) from labor l where l.C=CCODE_ID = cCCODE_ID and l.EMP_ID = e.EMP_ID and l.DELETED = 'N' and DATE_WORK = '2014-11-03' group by l.HOURS), 0) 'Monday',
IsNull((select sum(l.HOURS) from labor l where l.C=CCODE_ID = cCCODE_ID and l.EMP_ID = e.EMP_ID and l.DELETED = 'N' and DATE_WORK = '2014-11-04' group by l.HOURS), 0) 'Tuesday',
IsNull((select sum(l.HOURS) from labor l where l.C=CCODE_ID = cCCODE_ID and l.EMP_ID = e.EMP_ID and l.DELETED = 'N' and DATE_WORK = '2014-11-05' group by l.HOURS), 0) 'Wednesday',
IsNull((select sum(l.HOURS) from labor l where l.C=CCODE_ID = cCCODE_ID and l.EMP_ID = e.EMP_ID and l.DELETED = 'N' and DATE_WORK = '2014-11-06' group by l.HOURS), 0) 'Thursday',
IsNull((select sum(l.HOURS) from labor l where l.C=CCODE_ID = cCCODE_ID and l.EMP_ID = e.EMP_ID and l.DELETED = 'N' and DATE_WORK = '2014-11-07' group by l.HOURS), 0) 'Friday'
FROM ccode c
JOIN job j ON (l.JOB_ID = j.JOB_ID)
JOIN emp e ON (e.EMP_ID = l.EMP_ID)
LEFT JOIN extra ex ON (ex.JOB_ID = j.JOB_ID AND ex.EXTRA_ID = c.EXTRA_ID)
GROUP BY e.EMP_ID
ORDER BY j.JobID;

Open in new window

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now