?
Solved

SQL Syntax, Select

Posted on 2014-08-20
17
Medium Priority
?
210 Views
Last Modified: 2014-08-24
This is not correct but doesn't return any errors. Fields EXTRA/CO and CLASS return nulls. See attached ERD.

SELECT
CONCAT(e.EMPID, ' ',e.EFIRST, ' ', e.EMIDDLE, ' ', e.ELAST) 'EMPLOYEE',
ex.Extra_ID 'EXTRA/CO',
CONCAT(c.CostCode, ' ',c.DSCR) 'COST CODE',
CONCAT(ec.ECLASS, ' ',ec.DSCR)'CLASS',
l.HOURS 'HOURS'
FROM labor l
LEFT JOIN job j ON (j.JOB_ID = l.JOB_ID)
LEFT JOIN emp e ON (e.EMP_ID = l.JOB_ID)
LEFT JOIN extra ex ON (ex.JOB_ID = j.JOB_ID)
LEFT JOIN ccode c ON (c.EXTRA_ID = ex.EXTRA_ID)
LEFT JOIN eclass ec ON (ec.ECLASS_ID = l.ECLASS_ID)
WHERE j.JOB_ID = 1;
ERDTheEdgeLaborEntry.jpg
0
Comment
Question by:hdcowboyaz
  • 9
  • 8
17 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 40275165
The class would show as null when the join ec.ECLASS_ID = l.ECLASS_ID fails.
0
 

Author Comment

by:hdcowboyaz
ID: 40275182
Yes, It was simply a data problem. However, this query returns a Cartesian product.

SELECT
CONCAT(e.EMPID, ' ',e.EFIRST, ' ', e.EMIDDLE, ' ', e.ELAST) 'EMPLOYEE',
CONCAT(ex.ExtraID, ' ',ex.DSCR) 'EXTRA/CO',  
CONCAT(c.CostCode, ' ',c.DSCR) 'COST CODE',
CONCAT(ec.ECLASS, ' ',ec.DSCR)'CLASS',
l.HOURS 'HOURS'
FROM labor l
LEFT JOIN job j ON (j.JOB_ID = l.JOB_ID)
LEFT JOIN emp e ON (e.EMP_ID = l.JOB_ID)
LEFT JOIN extra ex ON (ex.JOB_ID = j.JOB_ID)
LEFT JOIN ccode c ON (c.EXTRA_ID = ex.EXTRA_ID)
LEFT JOIN eclass ec ON (ec.ECLASS_ID = l.ECLASS_ID)
WHERE j.JOB_ID = 1
AND l.DATE_WORK = '2014-08-20';
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 40275186
For EXTRA/CO, you're joining to the "extra" table through the "job" table using ON (ex.JOB_ID = j.JOB_ID) which is joined back to the labor table using ON (j.JOB_ID = l.JOB_ID). When one of those two joins is failing, you'll get a null value for EXTRA/CO. In that case you only know that the JOB_ID doesn't exist in either the "job" or "extra" table.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 35

Expert Comment

by:Terry Woods
ID: 40275188
To resolve a cartesian product issue, you need to narrow down where there are multiple rows in a table you're joining to. This might be because the data is bad, or because you aren't joining correctly.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 40275191
For example, this would happen if the "extra" table can have multiple rows for a job, or the "ccode" table could have multiple rows for an extra_id that it gets from the "extra" table.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 40275195
Do you really want to join an employee id to a job id, as you've shown in your latest sql? That wouldn't make sense:
 LEFT JOIN emp e ON (e.EMP_ID = l.JOB_ID)

Open in new window

0
 

Author Comment

by:hdcowboyaz
ID: 40275257
This was not right
LEFT JOIN emp e ON (e.EMP_ID = l.JOB_ID)
should be
LEFT JOIN emp e ON (e.EMP_ID = l.EMP_ID)

but when I do it I still get bad data. The reason I know I have a cartesian product is because the labor table only has two rows of data and only one row is JOB_ID = 1, so it should only return one row of data and it returns 24. Obviously, it cant return 24 rows of data since the table only has two.

It has something to do with the ccode table because there are 24 rows of data in it. When I delete one row of data from it...it returns one less row.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 40275284
Think about the meaning of the query. It sounds like you're wanting to list the labor hours for a job. Where there are 24 ccode rows for a job, that's a completely different thing. Which ccode row do you want for the labor hour row you're looking at? It's easy to just show one ccode, using min() or max(). Would that do?
0
 

Author Comment

by:hdcowboyaz
ID: 40275326
No, all I'm doing is showing any labor records that are for that job. There is only One row in the labor table for that job so it can only return one row of data. The most it could possibly return is two because there's only two rows of data. It's doing a Cartesian product on the ccode table. All the ccode table is used for, is a  look up to pull in the cost codes for that particular job.
0
 

Author Comment

by:hdcowboyaz
ID: 40275327
Did you look at my ERD?
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 40275337
Yes, but that doesn't tell me what's in the tables, or what you want in your results.
0
 

Author Comment

by:hdcowboyaz
ID: 40275354
None of that matters. There is one row of data in the labor table I am selecting the labor table. Therefore I should get one row of data. The reason I get 24 is because there are 24 rows of data in the cost code table. That is what is known as a Cartesian product. It's the one row times the 24. Evidently the way I am writing this select is not proper or it would not allow that.

This is 100% theory at this point.
0
 

Author Comment

by:hdcowboyaz
ID: 40275357
This is the latest

SELECT
CONCAT(e.EMPID, ' ',e.EFIRST, ' ', e.EMIDDLE, ' ', e.ELAST) 'EMPLOYEE',
CONCAT(ex.ExtraID, ' ',ex.DSCR) 'EXTRA/CO',  
CONCAT(c.CostCode, ' ',c.DSCR) 'COST CODE',
CONCAT(ec.ECLASS, ' ',ec.DSCR)'CLASS',
l.HOURS 'HOURS'
FROM labor l
LEFT JOIN emp e ON (e.EMP_ID = l.EMP_ID)
LEFT JOIN eclass ec ON (ec.ECLASS_ID = l.ECLASS_ID)
LEFT JOIN job j ON (j.JOB_ID = l.JOB_ID)
LEFT JOIN extra ex ON (ex.JOB_ID = j.JOB_ID)
LEFT JOIN ccode c ON (c.EXTRA_ID = ex.EXTRA_ID)
WHERE j.JOB_ID = 1
AND l.DATE_WORK = '2014-08-20';
0
 

Author Comment

by:hdcowboyaz
ID: 40275358
Simplifying it to this:

SELECT l.HOURS
FROM labor l
LEFT JOIN emp e ON (e.EMP_ID = l.EMP_ID)
LEFT JOIN eclass ec ON (ec.ECLASS_ID = l.ECLASS_ID)
LEFT JOIN job j ON (j.JOB_ID = l.JOB_ID)
LEFT JOIN extra ex ON (ex.JOB_ID = j.JOB_ID)
LEFT JOIN ccode c ON (c.EXTRA_ID = ex.EXTRA_ID)
WHERE j.JOB_ID = 1;
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 40275362
I understand the concept of a cartesian product.

You need to explain how to get the single cost code for a labor record; I can't tell you how to choose which of the 24 records is the correct one. I notice your ccode table has a JOB_ID in it, though the ER diagram shows no direct link to the job table. If that column does actually reliably contain the JOB_ID, you could join the ccode table to the job table instead of the extra table (as you're currently doing).

SELECT l.HOURS
FROM labor l
LEFT JOIN emp e ON (e.EMP_ID = l.EMP_ID)
LEFT JOIN eclass ec ON (ec.ECLASS_ID = l.ECLASS_ID)
LEFT JOIN job j ON (j.JOB_ID = l.JOB_ID)
LEFT JOIN extra ex ON (ex.JOB_ID = j.JOB_ID)
LEFT JOIN ccode c ON (c.JOB_ID = j.JOB_ID)
WHERE j.JOB_ID = 1; 

Open in new window

0
 

Author Comment

by:hdcowboyaz
ID: 40275378
The ERD shows the path labor - job -extra -ccode The path from job to ccode is through extra.

When I changed it as below it returns 6 rows of data.

SELECT l.HOURS
FROM labor l
LEFT JOIN emp e ON (e.EMP_ID = l.EMP_ID)
LEFT JOIN eclass ec ON (ec.ECLASS_ID = l.ECLASS_ID)
LEFT JOIN job j ON (j.JOB_ID = l.JOB_ID)
LEFT JOIN extra ex ON (ex.JOB_ID = j.JOB_ID)
LEFT JOIN ccode c ON (c.JOB_ID = ex.JOB_ID)
WHERE j.JOB_ID = 1;
0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 1500 total points
ID: 40275417
Can you join from the labor table to the ccode table, using the CCODE_ID in the labor table?
SELECT
CONCAT(e.EMPID, ' ',e.EFIRST, ' ', e.EMIDDLE, ' ', e.ELAST) 'EMPLOYEE',
CONCAT(ex.ExtraID, ' ',ex.DSCR) 'EXTRA/CO',  
CONCAT(c.CostCode, ' ',c.DSCR) 'COST CODE',
CONCAT(ec.ECLASS, ' ',ec.DSCR)'CLASS',
l.HOURS 'HOURS'
FROM labor l
LEFT JOIN emp e ON (e.EMP_ID = l.EMP_ID)
LEFT JOIN eclass ec ON (ec.ECLASS_ID = l.ECLASS_ID)
LEFT JOIN job j ON (j.JOB_ID = l.JOB_ID)
LEFT JOIN extra ex ON (ex.JOB_ID = j.JOB_ID)
LEFT JOIN ccode c ON (c.CCODE_ID = l.CCODE_ID)
WHERE j.JOB_ID = 1
AND l.DATE_WORK = '2014-08-20'; 

Open in new window

0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month17 days, 3 hours left to enroll

862 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