Need Help with SQL Select

[Gustavo1478]

Hello everyone, I need help writing a SQL select statement (not sure if this is the right place for this, but thought id give it a shot). I'm running MySQL 5.6, after doing some research on what i am trying to accomplish, I found that the process is called a "Pivot", but looking through all the examples online of how to do this Im still a bit not sure of how to adapt this to fit my needs. The following is my statement of how i imagine to do this:

SELECT t2.FULLNAME,
(SELECT MAX(COMPLETED_HRS)
	FROM courses
	WHERE FK_CLASS_ID = 2
	GROUP BY FK_MAIN_ID ) AS AirTools,
(SELECT MAX(COMPLETED_HRS)
	FROM courses
	WHERE FK_CLASS_ID = 1
	GROUP BY FK_MAIN_ID ) AS ABL
FROM COURSES t1
LEFT JOIN main t2 ON t1.FK_MAIN_ID = t2.PK_MAIN_ID
GROUP BY t2.PK_MAIN_ID

I have about 200+ classes that we use but I only want to query about 30, which is why I thought to use the individual select statements in subqueries, the problem is that the sub-queries return more than one row which apparently is not allowed. Any suggestions or point in the right direction is more than welcome.

[Gustavo1478]

Re: Need Help with SQL Select

UPDATE

Well I accomplished what I was trying to do, its not pretty but it works and query only takes about 3 Seconds. basically I created a view for every class I want to Isolate and then created a view that left joins all the views created for each class.

SELECT t2.FULLNAME,
t2.LOCAL_BRANCH,
t2.Area,
t2.BRANCH,
t2.SSN,
t2.PHONE,
t2.PERIOD,
t2.RSI_TOTAL,
t2.GRAND_TOTAL,
t2.OJT,
t5.Voice,
t35.CE101,
t36.FLS,
t6.FaidCPRAED,
t7.OSHA10,
t37.EHHW,
t38.TCF1,
t39.OSHA30,
t8.SCU,
t9.SCB,
t4.AirTools,
t10.AsphaltRR,
t11.BTDuties,
t12.BTScaff_Erect_Diss,
t13.BTForklift,
t14.ConcreteRR,
t15.Landscape1,
t16.PipeLaying,
t17.ProcessPiping1,
t18.GenCon,
t3.ABL,
t19.AirToolsAwareness,
t20.FallProtection,
t21.SkidSteer1,
t22.SkidSteer2,
t23.TrenchPlateProtection,
t24.ClassA,
t25.ClassB,
t26.HazCom,
t27.Respro,
t28.AsbestosAwareness,
t29.CSA,
t30.LeadAwareness,
t31.HTWW,
t32.LeadWorker,
t33.AsbestosWorker,
t34.GrandFather
FROM COURSES t1
LEFT JOIN main t2 ON t1.FK_MAIN_ID = t2.PK_MAIN_ID
LEFT JOIN vw_SchoolALL_ABL t3 ON t1.FK_MAIN_ID = t3.fk_main_id
LEFT JOIN vw_SchoolAll_AirTools t4 ON t1.FK_MAIN_ID = t4.fk_main_id
LEFT JOIN vw_SchoolAll_Voice t5 ON t1.FK_MAIN_ID = t5.fk_main_id
LEFT JOIN vw_SchoolAll_FaidCPRAED t6 ON t1.FK_MAIN_ID = t6.fk_main_id
LEFT JOIN vw_SchoolAll_OSHA10 t7 ON t1.FK_MAIN_ID = t7.fk_main_id
LEFT JOIN vw_SchoolAll_SCU t8 ON t1.FK_MAIN_ID = t8.fk_main_id
LEFT JOIN vw_SchoolAll_SCB t9 ON t1.FK_MAIN_ID = t9.fk_main_id
LEFT JOIN vw_SchoolAll_AsphaltRR t10 ON t1.FK_MAIN_ID = t10.fk_main_id
LEFT JOIN vw_SchoolAll_BTDuties t11 ON t1.FK_MAIN_ID = t11.fk_main_id
LEFT JOIN vw_SchoolAll_BTScaff_Erect_Diss t12 ON t1.FK_MAIN_ID = t12.fk_main_id
LEFT JOIN vw_SchoolAll_BTForklift t13 ON t1.FK_MAIN_ID = t13.fk_main_id
LEFT JOIN vw_SchoolAll_ConcreteRR t14 ON t1.FK_MAIN_ID = t14.fk_main_id
LEFT JOIN vw_SchoolAll_Landscape1 t15 ON t1.FK_MAIN_ID = t15.fk_main_id
LEFT JOIN vw_SchoolAll_PipeLaying t16 ON t1.FK_MAIN_ID = t16.fk_main_id
LEFT JOIN vw_SchoolAll_ProcessPiping1 t17 ON t1.FK_MAIN_ID = t17.fk_main_id
LEFT JOIN vw_SchoolAll_GenCon t18 ON t1.FK_MAIN_ID = t18.fk_main_id
LEFT JOIN vw_SchoolAll_AirToolsAwareness t19 ON t1.FK_MAIN_ID = t19.fk_main_id
LEFT JOIN vw_SchoolAll_FallProtection t20 ON t1.FK_MAIN_ID = t20.fk_main_id
LEFT JOIN vw_SchoolAll_SkidSteer1 t21 ON t1.FK_MAIN_ID = t21.fk_main_id
LEFT JOIN vw_SchoolAll_SkidSteer2 t22 ON t1.FK_MAIN_ID = t22.fk_main_id
LEFT JOIN vw_SchoolAll_TrenchPlateProtection t23 ON t1.FK_MAIN_ID = t23.fk_main_id
LEFT JOIN vw_SchoolAll_ClassA t24 ON t1.FK_MAIN_ID = t24.fk_main_id
LEFT JOIN vw_SchoolAll_ClassB t25 ON t1.FK_MAIN_ID = t25.fk_main_id
LEFT JOIN vw_SchoolAll_HazCom t26 ON t1.FK_MAIN_ID = t26.fk_main_id
LEFT JOIN vw_SchoolAll_Respro t27 ON t1.FK_MAIN_ID = t27.fk_main_id
LEFT JOIN vw_SchoolAll_AsbestosAwareness t28 ON t1.FK_MAIN_ID = t28.fk_main_id
LEFT JOIN vw_SchoolAll_CSA t29 ON t1.FK_MAIN_ID = t29.fk_main_id
LEFT JOIN vw_SchoolAll_LeadAwareness t30 ON t1.FK_MAIN_ID = t30.fk_main_id
LEFT JOIN vw_SchoolAll_HTWW t31 ON t1.FK_MAIN_ID = t31.fk_main_id
LEFT JOIN vw_SchoolAll_LeadWorker t32 ON t1.FK_MAIN_ID = t32.fk_main_id
LEFT JOIN vw_SchoolAll_AsbestosWorker t33 ON t1.FK_MAIN_ID = t33.fk_main_id
LEFT JOIN vw_SchoolAll_GrandFather t34 ON t1.FK_MAIN_ID = t34.fk_main_id
LEFT JOIN vw_SchoolAll_CE101 t35 ON t1.FK_MAIN_ID = t35.fk_main_id
LEFT JOIN vw_SchoolAll_FLS t36 ON t1.FK_MAIN_ID = t36.fk_main_id
LEFT JOIN vw_SchoolAll_EHHW t37 ON t1.FK_MAIN_ID = t37.fk_main_id
LEFT JOIN vw_SchoolAll_TCF1 t38 ON t1.FK_MAIN_ID = t38.fk_main_id
LEFT JOIN vw_SchoolAll_OSHA30 t39 ON t1.FK_MAIN_ID = t39.fk_main_id
WHERE t2.JAC_STATUS = 'Active' OR t2.JAC_STATUS = 'Active Probation'
GROUP BY t2.PK_MAIN_ID
ORDER BY t2.LOCAL_BRANCH, t2.AREA, t2.BRANCH, t2.FULLNAME

[Michael1954]

Re: Need Help with SQL Select

You can look at Case if you are not happy with this approach.

David Kates has done this programmatically and sells it for $99 I believe.


Google Alphasoftware Pivot grid David Kates.


https://www.youtube.com/watch?v=rnus8DYt01Q

[GGandhi]

Re: Need Help with SQL Select

it is really impressive. again it proves that sqlserver is capable of running the script faster than it takes to read the script. very good.
i do have some questions however.

i notice that course table is providing basically foreign key to many tables, and not actually any data to the query. is that correct or am i wrong?

also you have taken a single field from many tables, is it because the tables are normalized to a point that they are fragmented?

when you use foreign key on a table or a main key, i see that you name them fk_main_id, pk_main_id respectively. will you be able to remember what table it is referring to or someone who will be working on this later on, will they able to decipher what table it is referring to?
on the other hand if you have tables order_head and it has primary key id or order_id and the order_detail table with primary key as order_detail_id or id and foreign key as order_head_id then it will be easy to remember the connections, is it not correct?

last but not the least. as you found out this is the best example for quoted string construction for sql statement, if you were to write this in one single line, the line will end in wisconsin. gives you high readability and single page code as opposed long snaking code that runs the screen buffer out.

again really impressive to get it working.

[Gustavo1478]

Re: Need Help with SQL Select

You can look at Case if you are not happy with this approach.

David Kates has done this programmatically and sells it for $99 I believe.


Google Alphasoftware Pivot grid David Kates.


https://www.youtube.com/watch?v=rnus8DYt01Q

Thanks for the video link, this is exactly what I am looking for but in a report style.

[Gustavo1478]

Re: Need Help with SQL Select

it is really impressive. again it proves that sqlserver is capable of running the script faster than it takes to read the script. very good.
i do have some questions however.

i notice that course table is providing basically foreign key to many tables, and not actually any data to the query. is that correct or am i wrong?

also you have taken a single field from many tables, is it because the tables are normalized to a point that they are fragmented?

when you use foreign key on a table or a main key, i see that you name them fk_main_id, pk_main_id respectively. will you be able to remember what table it is referring to or someone who will be working on this later on, will they able to decipher what table it is referring to?
on the other hand if you have tables order_head and it has primary key id or order_id and the order_detail table with primary key as order_detail_id or id and foreign key as order_head_id then it will be easy to remember the connections, is it not correct?

last but not the least. as you found out this is the best example for quoted string construction for sql statement, if you were to write this in one single line, the line will end in wisconsin. gives you high readability and single page code as opposed long snaking code that runs the screen buffer out.

again really impressive to get it working.

Hello Ghandi,

(1)The main table that I am querying is the courses table and it does inherit a parent key in the form of fk_main_id whenever a student takes a class.
(2) The tables are normalized, the two tables Im working with ive called (1) Courses and (2) main, courses table holds all class information, date attended, date completed, completed hours etc. and the MAIN table holds student information, name, address, ssn etc. Currently the courses table hold all data in the form of rows but I wanted to turn the rows into columns. A student can take the same class multiple times but I just needed their most recent date of attendance AND the max hours completed (regardless if its the most recent I am looking for the class with most completed hours) which is why I created a separate view for each class i wanted to isolate, Also some classes have an expiration date so along with the filter just mentioned I need to weed out expired classes. The only way I knew how to do this was to create views for each specific class since some have unique filters and others dont.
(3) Each table uses its own name along with PK for primary key, and when foreign tables use the PK of a parent I name the field FK along with the table name, I think that's what you were asking, right?

[Gustavo1478]

Re: Need Help with SQL Select

Heres the structure of each one of my views that I Left Joined, some classes have the BETWEEN date filter and others do not.

SELECT
	t2.FULLNAME,
	fk_main_id,
	MAX(COMPLETED_HRS) AS FLS
FROM
	courses t1
LEFT JOIN main t2 ON t1.FK_MAIN_ID = t2.pk_main_ID
WHERE
	FK_CLASS_ID = 30
AND (
	t1.COMPLETION_DATE BETWEEN DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
	AND CURDATE()
)
GROUP BY
	FK_MAIN_ID