hdcowboyaz
asked on
SQL Syntax, Select
This query works and outputs one row of data.
This query works and displays one row and one field only, but I want to add this column SUM(l.HOURS) AS Hours_JTD to the above query. This will add one table labor l to the above query.
I am attaching the ERD as well as the currrent output of the first query.
ERD.jpg
Output.jpg
SELECT
st.SCTYPE 'Desc', st.UM 'UoM', FORMAT(SUM(c.POPU), 0) 'Orig Qty', ROUND(SUM(c.POPU) / SUM(c.POPU / c.POUEST), 0) 'Orig PR',
c.POEST 'Orig HR', FORMAT(SUM(c.POPU / c.POUEST), 0) 'Total Hours', FORMAT(SUM(c.POPU / c.POUEST) * c.POEST, 0) 'Est Cost',
ROUND(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU), 4) 'Est Unit Cost', m.Placed_JTD
FROM ccode c
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode s ON c.SCCODE_ID = s.SCCODE_ID
INNER JOIN sctype st ON s.SCTYPE = st.SCTYPE_ID
CROSS JOIN (
SELECT SUM(m.PLACEMENT) Placed_JTD
FROM materials m
INNER JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
INNER JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype st ON s.SCTYPE = st.SCTYPE_ID
INNER JOIN job j ON m.JOB_ID = j.JOB_ID
WHERE (st.SCTYPE = 'rebar' OR st.SCTYPE = 'caissons')
AND m.JOB_ID = 7398
AND m.DATE_PLACE <= '2013-10-6'
AND c.TM = 'N'
AND (m.DELETED != 'Y'OR m.DELETED IS NULL)
AND st.DELETED = 'N'
) m
WHERE (st.SCTYPE_ID = 1 OR st.SCTYPE_ID = 7)
AND j.JOB_ID = 7398
GROUP BY st.SCTYPE;
This query works and displays one row and one field only, but I want to add this column SUM(l.HOURS) AS Hours_JTD to the above query. This will add one table labor l to the above query.
SELECT SUM(l.HOURS) AS Hours_JTD
FROM labor l, ccode c, sccode s, sctype st
WHERE (c.CCODE_ID = l.CCODE_ID)
AND (s.SCCODE_ID = c.SCCODE_ID)
AND (st.SCTYPE_ID = s.SCTYPE)
AND (st.SCTYPE = 'rebar' OR st.SCTYPE = 'caissons')
AND l.JOB_ID = 7398
AND l.DATE_WORK <= '2013-10-6'
AND c.TM = 'N'
AND (l.DELETED != 'Y' OR l.DELETED IS NULL)
GROUP BY l.JOB_ID;
I am attaching the ERD as well as the currrent output of the first query.
ERD.jpg
Output.jpg
ASKER
Thats nice theory but it doesn't solve the query.
Ok. Can you add the sql to recreate the tables with some test data, so I can check?
My comment was a guide to apply, but you did not comment on the result of this application. This is what I intend to do when you send the script to recreate the issue.
My comment was a guide to apply, but you did not comment on the result of this application. This is what I intend to do when you send the script to recreate the issue.
ASKER
I'm putting together he tables and data. Should have by tonight.
ASKER
Current Query for new data attached as sqdump
SELECT
st.SCTYPE 'Desc', st.UM 'UoM',
FORMAT(SUM(c.POPU), 0) 'Orig Qty',
ROUND(SUM(c.POPU) / SUM(c.POPU / c.POUEST), 0) 'Orig PR',
FORMAT(c.POEST, 2) 'Orig HR',
FORMAT(SUM(c.POPU / c.POUEST), 0) 'Total Hours',
FORMAT(SUM(c.POPU / c.POUEST) * c.POEST, 0) 'Est Cost',
ROUND(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU), 4) 'Est Unit Cost',
m.Placed_JTD 'Placed_JTD',
m.Placed_JTD 'Hours_JTD',
m.Placed_JTD 'Cost_JTD',
m.Placed_JTD 'Actual PR',
m.Placed_JTD 'Unit Cost JTD',
m.Placed_JTD 'Est Cost',
m.Placed_JTD 'Variance',
m.Placed_JTD 'Avg HR'
FROM ccode c
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode s ON c.SCCODE_ID = s.SCCODE_ID
INNER JOIN sctype st ON s.SCTYPE = st.SCTYPE_ID
CROSS JOIN (
SELECT SUM(m.PLACEMENT) Placed_JTD
FROM materials m
INNER JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
INNER JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype st ON s.SCTYPE = st.SCTYPE_ID
INNER JOIN job j ON m.JOB_ID = j.JOB_ID
WHERE m.JOB_ID = 7001
AND m.DATE_PLACE <= '2013-10-6'
AND c.TM = 'N'
AND (m.DELETED != 'Y'OR m.DELETED IS NULL)
AND st.DELETED = 'N'
) m
WHERE (st.SCTYPE_ID = 1 OR st.SCTYPE_ID = 7)
AND j.JOB_ID = 7001
GROUP BY st.SCTYPE;
steel-10-19-13.sql
ASKER
Can't see Hours in labor table.
I suggest you recreate the issue simplifying the two queries using up to 3 tables.
Doing that you may solve the problem yourself.
I suggest you recreate the issue simplifying the two queries using up to 3 tables.
Doing that you may solve the problem yourself.
ASKER
I've attached a new ERD which shows l.HOURS. There is to be only one query. You have the tables with data and an ERD. All 7 tables are requried, This is a simple query. There are more columns remaining once this is resloved.
ERD.jpg
ERD.jpg
"This is a simple query"
For me it is not. I expect 3 tables including labor, with 3 to 4 fields.
List few records for each input table, and list expected output.
Once you find the idea, you may expand required tables and fields as you wish.
For me it is not. I expect 3 tables including labor, with 3 to 4 fields.
List few records for each input table, and list expected output.
Once you find the idea, you may expand required tables and fields as you wish.
Have you considered not trying to do everything in the one query?
From experience I've always found it easier to break it up into much smaller queries and create views before bringing it all together. That way you can test and know the data is correct at each stage. It may not be as efficient but at least you know it works and where you're going wrong. Also possible to migrate it all to one query later if you wanted to. You may also find these separate queries/views can be re used in other queries.
I'll try to find some time to set up an example for you with your structure.
From experience I've always found it easier to break it up into much smaller queries and create views before bringing it all together. That way you can test and know the data is correct at each stage. It may not be as efficient but at least you know it works and where you're going wrong. Also possible to migrate it all to one query later if you wanted to. You may also find these separate queries/views can be re used in other queries.
I'll try to find some time to set up an example for you with your structure.
ASKER
It's already done by separate queries this is the point I'm trying to make one query. Can no one out there do this?
Ok go easy, I've just come across this thread. It was your cross join sub query that led me to believe you could simplify this query further by removing that as well. As I said, give me some time to review the structure and ERD
Can you confirm if this is correct?
Do you want to display the aggregate data of Placed_JTD and Hours_JTD for each SCTYPE?
SCTYPE_ID SCTYPE
1 rebar
7 caissons
Also why do you want to display same Placed_JTD and Hours_JTD for two different SCTYPE?Do you want to display the aggregate data of Placed_JTD and Hours_JTD for each SCTYPE?
Late commer but on query 1 you are grouping on st.SCTYPE and in the labour query you are grouping on l.hours - seems to be a conflict.
Have you considered defining the second query as a view (but without the filter on JOBID) and joining to that on to the first query by jobID?
Have you considered defining the second query as a view (but without the filter on JOBID) and joining to that on to the first query by jobID?
Info to help needed: From input and expected output, one can think of process. If process is known, one can think of output and ways to reorganize input in proper form.
http:#a39587360
http:#a39587360
Your existing larger query already contains a technique to combine 2 queries into one - by using "cross apply". I think that was the result of a similar question.
Alternatively, the same logic that was used in another prior question can be applied here, a correlated subquery.
You have 2 working queries, both are returning just one row, and one of these just one column - so that second small query can be embedded into the selection clause of the larger query (or embedded as a cross apply).
Same caveat as last time - a correlated subquery in the selection list isn't necessarily the most efficient way - but with such small result sets it is not going to be noticeable here.
Given this same need has been asked several times it will probably be worthwhile if you try it first, if you get into difficulty with it then let us know.
-----------------
If you wanted a more sophisticated solution than these approaches we would need all table definitions, sample data for each table - as well as the expected results (while we can read sql quite easily it is quite another predicting the effect of joins without being able to asses a query with data).
Alternatively, the same logic that was used in another prior question can be applied here, a correlated subquery.
You have 2 working queries, both are returning just one row, and one of these just one column - so that second small query can be embedded into the selection clause of the larger query (or embedded as a cross apply).
Same caveat as last time - a correlated subquery in the selection list isn't necessarily the most efficient way - but with such small result sets it is not going to be noticeable here.
Given this same need has been asked several times it will probably be worthwhile if you try it first, if you get into difficulty with it then let us know.
-----------------
If you wanted a more sophisticated solution than these approaches we would need all table definitions, sample data for each table - as well as the expected results (while we can read sql quite easily it is quite another predicting the effect of joins without being able to asses a query with data).
ASKER
Sharath_123
Can you confirm if this is correct?
SCTYPE_ID SCTYPE
1 rebar
7 caissons
- Yes, this is correct
Also why do you want to display same Placed_JTD and Hours_JTD for two different SCTYPE?
- We are combining the results of 1 & 7 together.
Do you want to display the aggregate data of Placed_JTD and Hours_JTD for each SCTYPE
- Yes, but I was trying to make the query simple so I used the first SCTYPE (1 & 7) in the query. I was going to use PHP to run through the rest of them.
julianH
On query 1 you are grouping on st.SCTYPE and in the labour query you are grouping on l.hours - seems to be a conflict.
- Yes the querries are grouped differently. They return completely different values;
Have you considered defining the second query as a view (but without the filter on JOBID) and joining to that on to the first query by jobID?
- I don't know views enough to answer that but I have thought they may be the way to go.
hsnar
Info to help needed: From input and expected output, one can think of process. If process is known, one can think of output and ways to reorganize input in proper form.
- I'm not looking for generic statements
PortletPaul
- Yes I agree. I was going to try what you did on the last one. I will see if it works and put up the results.
Can you confirm if this is correct?
SCTYPE_ID SCTYPE
1 rebar
7 caissons
- Yes, this is correct
Also why do you want to display same Placed_JTD and Hours_JTD for two different SCTYPE?
- We are combining the results of 1 & 7 together.
Do you want to display the aggregate data of Placed_JTD and Hours_JTD for each SCTYPE
- Yes, but I was trying to make the query simple so I used the first SCTYPE (1 & 7) in the query. I was going to use PHP to run through the rest of them.
julianH
On query 1 you are grouping on st.SCTYPE and in the labour query you are grouping on l.hours - seems to be a conflict.
- Yes the querries are grouped differently. They return completely different values;
Have you considered defining the second query as a view (but without the filter on JOBID) and joining to that on to the first query by jobID?
- I don't know views enough to answer that but I have thought they may be the way to go.
hsnar
Info to help needed: From input and expected output, one can think of process. If process is known, one can think of output and ways to reorganize input in proper form.
- I'm not looking for generic statements
PortletPaul
- Yes I agree. I was going to try what you did on the last one. I will see if it works and put up the results.
- Yes the querries are grouped differently. They return completely different values;Then how do you expect to combine them.
A view is just a named query.
So
CREATE VIEW vwLabourHours AS
SELECT SUM(l.HOURS) AS Hours_JTD
FROM labor l, ccode c, sccode s, sctype st
WHERE (c.CCODE_ID = l.CCODE_ID)
AND (s.SCCODE_ID = c.SCCODE_ID)
AND (st.SCTYPE_ID = s.SCTYPE)
AND (st.SCTYPE = 'rebar' OR st.SCTYPE = 'caissons')
AND c.TM = 'N'
AND (l.DELETED != 'Y' OR l.DELETED IS NULL)
GROUP BY l.JOB_ID;
You can then achieve the same results by
SELECT * FROM vwLabourHours WHERE l.JOB_ID = 7398 AND l.DATE_WORK <= '2013-10-6';
You can also then join the view into the first query and link it on the JOB_ID.
Of course you could simply join the second query to the first as a subquery - but the view approach might make it easier - it could get quite convoluted the other way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
See above
ASKER
Leave me alone
ASKER
I just did
SELECT q1.f1, q1f2, q2.f1
FROM q1 INNER JOIN q2 ON q1.f = q2.f;
replace q1 and q2 with your queries.
SELECT q1.f1, q1f2, q2.f1
FROM (SELECT ..... ) AS q1 INNER JOIN (SELECT ... ) AS q2
ON q1.f = q2.f;