?
Solved

SQL - Cross joins

Posted on 2014-11-17
7
Medium Priority
?
143 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

 
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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
Suggested Courses

770 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