SQL Syntax, Select

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
hdcowboyazAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
The class would show as null when the join ec.ECLASS_ID = l.ECLASS_ID fails.
0
hdcowboyazAuthor Commented:
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
Terry WoodsIT GuruCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Terry WoodsIT GuruCommented:
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
Terry WoodsIT GuruCommented:
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
Terry WoodsIT GuruCommented:
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
hdcowboyazAuthor Commented:
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
Terry WoodsIT GuruCommented:
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
hdcowboyazAuthor Commented:
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
hdcowboyazAuthor Commented:
Did you look at my ERD?
0
Terry WoodsIT GuruCommented:
Yes, but that doesn't tell me what's in the tables, or what you want in your results.
0
hdcowboyazAuthor Commented:
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
hdcowboyazAuthor Commented:
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
hdcowboyazAuthor Commented:
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
Terry WoodsIT GuruCommented:
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
hdcowboyazAuthor Commented:
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
Terry WoodsIT GuruCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.