Nested query

Goal: pull out the title name of the fire safety components that have not yet been followed by a user.

I am building a database where I can track users on going fire safety training or drills. A report is created each time a drill is performed. This report will indicate what the drill was about, what fire safety components where instructed and who attended the drill. Each user have a profile they can go to in order to review their training progress. I am echoing the fire safety components that was followed and also want to show in another area the fire safety component that currently exist but not yet followed by a user.

tables:, ,

firesafety

PKEY->id
with a bunch of other fields i dont need here

fsattendances

PKEY->id
FKEY->fireSafetyId
field ā†’ attendee (userid)

fstraining,

PKEY->id
FKEY->fireSafetyId
FKEY->componentId

fscomponents

PKEY-> id
componentTtile
and some other field I donā€™t really need to pull out

query I have so far:

" SELECT componentTitle FROM fscomponents WHERE fsattendances LEFT JOIN firesafety ON fsattendances.fireSafetyId = firesafety.id LEFT JOIN fstraining LEFT JOIN fscomponents ON fstraining.FSComponentId = fscomponents.id ON firesafety.id = fstraining.fireSafetyId WHERE fsattendances.attendee <> $userid AND fscomponents.componentTitle=NULL"

which doesnā€™t work. I am returning the same exact results of my query that is checking for fscomponent that have been attended to.

example:

user was present at 3 fire drills where fire safety component: fire extinguisher, Room Search and Radio Communication were instructed.

those are only 3 components from a list of lets say 10

so I would in essence need to pull the 7 components that have still to be learned by the user and echo these componentTitle(s) on their profile

It is very hard to follow the logic of your DB tables and without data to see the relations, I will offer this query.

SELECT 
fsc.componentTitle 
FROM fscomponents as fsc 
    LEFT JOIN fstraining as fst  
        ON fst.componentId <>  fsc.id 
    LEFT JOIN fsattendances as fsa 
        ON fsa.fireSafetyId =  fst.fireSafetyId
WHERE fsa.attendee = $userid

Maybe attaching sql files might shed light on things.

Thanks for giving it a shot. Unfortunatley it does not work.

here is something that does work(but it is from Microsoft Access database) I been getting a big chunk of my slq statements from Access since it is so quick to build. One thing I still have to learn is how to interpret it so mysql and php can work it.

first query is to select all the component a user has participated in. I get this by the following query and which becomes my nested query: called ā€˜userAttendedā€™

SELECT fsattendances.attendee, fscomponents.componentTitle
FROM fsattendances LEFT JOIN (fstraining LEFT JOIN fscomponents ON fstraining.FSComponentId=fscomponents.id) ON fsattendances.fireSafetyId=fstraining.fireSafetyId
WHERE (((fsattendances.attendee)="userid"));

then I run another an ā€œunmatched queryā€ from the wizard and used the first query to compare it to.

SELECT fscomponents.componentTitle
FROM fscomponents LEFT JOIN userAttended ON fscomponents.[componentTitle] = userAttended.[componentTitle]
WHERE (((userAttended.componentTitle) Is Null));

and this works . I just donā€™t know how to join the two queries into one on my web page.

The logic as best as I can explain it goes like this:

I have users who attend fire drill every months. These users ID get inserted in the ā€˜fsattendanceā€™ table along with the ID number of the particular drill they attended. This ID is generated from a report that is filled up after every fire drill and saved into a table called ā€˜firesafetyā€™ along with the ID of the fire safety component that was practiced. The fire safety components are a bunch of exercise like fire exthinguisher operation, room search, radio communication and so on. Every month a different component or a combination of component are instructed to workers. So the fire safety report and the fire safety compnent gets saved as a record into a table called ā€˜fstraining.ā€™ I have to use this type of logic in order to keep my tables ā€œnormalā€

Well I do understand the logic of what you are trying to do. What isnā€™t so clear is the relationship of the tables. Looking at it, fireSafetyId or id from firesafety table, this seems to be a primary key used in most tables and to me this would be a general group id/name with components being a sub group, yet in your attendances table you are not logging which component they have learned just the general fireSafetyId.

ok I understand what you are saying. I use ā€œIDā€ as a primary key for all my tables. Where there are Foreign keys pointing at the primary key I always used the whole name.

Example: tbl FireSafety primary key is ID. anywhere else this primary key is used as a foreign key I use the full name :fireSafetyId

relationship flow like this:
the driving force behind this exercise is the fire drill report. ->firesafety table. the ID of this record is used as a foreign key in the [fstraining] table along with another foreign key for the safety component that was instructed. The last table is the attendance which has a foreign key of the user and of the [fireSafety] record.

does this help?

Maybe.
Also I saw no mention of userAttended table earlier in this thread, so I can now see where you are storing component learned.

This one?

the fire safety component is stored in this one:

IN post#3 above you use userAttended.[componentTitle] so I assume you have a userAttended tableā€¦ right?

If so what fields does it have? id, user_id, componentTitle???

Yes you are correct.

in post #3 is my attempt to join the two queries that I succesfully retrieved the data that I am after while using Microst Access. Which failed in the script that I have written here.

the name of the user is $userid. I pickup the user id from the table through $_SESSION

the exercise is three folds:

first: (and first page)

they completed the report. Once this report has been completed I store the ID of this [ tbl firesafety]record in $_session and tag it to the second step as a hidden input tag.

second step:(redirected to another page for attendances)
I get the user to tic all the users who attended the fire drill and then when they save the form, the script pickup the ID of the firesafety record at the same time from the hidden input tag and save into [tbl fsattendances]

last step (redirected to last page fire safety components)
once again I hide the firesafety id in a hidden tag and ask the user to tic which component that was instructed during the drill and save to [tbl fstraining]

I have no choice to use this method given the audience I deal with. I tried various ways in the past and this is the only one that seems to work with everyone. I am trying to transfer this application that was built in microsft Access into web format

here is the script if it can help?

       <section id="CompletedComponents" class="sectionLeft">
        <h3 class="pow">Completed Fire Safety Training Components</h3>
        	<?php 
    		$userid = $user->data()->id;
    		$rs = DB::getInstance()->get('fsattendances LEFT JOIN firesafety ON fsattendances.fireSafetyId = firesafety.id LEFT JOIN fstraining LEFT JOIN fscomponents ON fstraining.FSComponentId = fscomponents.id ON firesafety.id = fstraining.fireSafetyId', array( 'fsattendances.attendee', '=', $userid));
    		if($rs->count()){
    			echo '<table>';
    		
    				echo '<th class="th">Fire Safety Component</th><th class="th">Completed On</th>';
    					echo '<tr>';
    					  foreach($rs->results() as $list){
    						  echo	'<td class="tdLeft"> <a  href="firesafety.php?id='.escape($list->id).'">'.escape($list->componentTitle).'</a></td>';
    						  echo	'<td class="tdLeft">'.date ("F d, Y",strtotime($list->FSDate)).'</td>';
    					echo '</tr>';	
    						}	
    		}
    		else
    		  {echo 'You have not completed any Fire Safety Component Training';}
    		echo'</table>';
    		?>
        </section>
      
      
          <section class="sectionLeft">
        <h3 class="pow">Fire Safety Training Components that have NOT been completed yet</h3>
        	<?php 
    		$userid = $user->data()->id;
// this is the one that doesn't work/////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    		$rs = DB::getInstance()->query(" 
    			SELECT fsattendances.attendee, fscomponents.componentTitle
    FROM fsattendances LEFT JOIN (fstraining LEFT JOIN fscomponents ON fstraining.FSComponentId=fscomponents.ID) ON fsattendances.fireSafetyId=fstraining.fireSafetyId
    WHERE (((fsattendances.attendee)!='$userid')");
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////    
    		
    
    		if($rs->count()){
    			echo '<table>';
    		
    				echo '<th class="th">Fire Safety Component</th>';
    					echo '<tr>';
    					  foreach($rs->results() as $list){
    						
    						  echo	'<td class="tdLeft"> <a  href="firesafety.php?id='.escape($list->id).'">'.escape($list->componentTitle).'</a></td>';
    						  Session::flash('home', '<<<----Click Fire Safety menu link to view all Fire Safety Components----->>>.');
    					echo '</tr>';	
    		
    					  }	
    		}else{echo 'There is no existing Fire Training Component at the monent';}
    		echo'</table>';
    		?>
        </section>

So, for a given UserID, Find the status of all components.
Assuming that someone who attended a firesafety attended ALL components covered by that firesafety:

SELECT fscomponents.componentName, a.attendee FROM (SELECT fscomponents.componentName,fsattendances.attendee FROM fscomponents LEFT JOIN fstraining ON fscomponents.id = fstraining.componentId LEFT JOIN fsattendances ON fstraining.fireSafetyId = fsattendances.fireSafetyId WHERE fsattendances.attendee = 1) AS a RIGHT JOIN fscomponents ON a.componentName = fscomponents.componentName

(Only partially tested)
Should return a list of all component names, and either NULL or the userid. If NULL, the user has not attended the component yet.

1 Like

Had to change componentName to componentTitle to work with fields I have but results were as expected. Well done! :wink:

Iā€™m not sure about that unless when you say ā€œAll component attendedā€ you mean all components that was related to the particular fire safety drill was attended? Otherwise we cannot assume that someone will attend every single fire safety Drill in the future.

I tried the new sql and unfortunately I am not getting the result that I am looking for; basically returning attended components.

What I am looking for is to show a list of components that exists and have still not been attended by the user.

how did you get that to work Drummin? have you built tables with data to see the results?

Try with this data. at the end your result would need to be ->>>>component 5 title<<<<ā€“

tbl firesafety
ID SFDate
1 2015-06-01
2 2015-06-02
3 2015-06-03
4 2015-06-04
5 2015-06-05

tbl fsattendances
ID attendee fireSafetyId
1 1 1
2 1 2
3 1 3

tbl fscomponents
ID componentTitle
1 component 1 title
2 component 2 title
3 component 3 title
4 component 4 title
5 component 5 title

tbl fstraining
ID fireSafetyId FSComponentId
1 1 1
2 1 2
3 2 3
4 3 4
5 4 1
6 4 2
7 5 5

Component1
1
Component2
1
Component3
1
Component4
1
Component5
NULL

Which is the correct result.

Yes Jaady, I see what you mean but you should be able to use the null id in the result set to display any component that this user has not attended.

I have one question:

the last part of the query you show:

AS a RIGHT JOIN fscomponents ON a.componentTitle = fscomponents.componentTitle");

Where do you get the " a.componentTitle " ? I do not have anything relating to fire safety component in my attendance table? I only have 3 fields: id, fireSafetyId, attendee

copy of the print_r()

DB Object
(
[_pdo:DB:private] => PDO Object
(
)

[_query:DB:private] => PDOStatement Object
    (
        [queryString] =>  
		SELECT fscomponents.componentTitle, a.attendee FROM (SELECT fscomponents.componentTitle,fsattendances.attendee 
		FROM fscomponents 
		LEFT JOIN fstraining 
		ON fscomponents.id = fstraining.componentId 
		LEFT JOIN fsattendances 
		ON fstraining.fireSafetyId = fsattendances.fireSafetyId 
		WHERE fsattendances.attendee = 1) AS a RIGHT JOIN fscomponents ON a.componentTitle = fscomponents.componentTitle
    )

[_error:DB:private] => 1

Note the parenthesis. I do an entire subquery, which i use as a temporary table in my outer query, defined as ā€œaā€. (Not the most inventive temporary table name iā€™ve ever used, but it works).

If you notice in the inner select, i select fscomponents.componentTitle and fsattendances.attendee as my returns. That means that ā€œaā€ is a table with two columns - componentTitle and attendee (I could have renamed them, but chose not to).