Solved

SQL - Cross joins

Posted on 2014-11-17
7
134 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
ID: 40448632
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
ID: 40448643
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
ID: 40448646
Cross Joins - SQL
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 14

Expert Comment

by:quizwedge
ID: 40448721
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
ID: 40448739
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
ID: 40448752
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
ID: 40450224
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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