Problem with multiple select statements in a stored procedure

[questhaven]

Hi - I am trying to create a program that searches through a log file and create various type of reports based on the information selected in check boxes. Unfortunately the information contained within the log can be different from each other, and require joins of different tables. I've tried to put all the results into a temporary table but it does not seem to be working because of the fact that some queries require joins on tables that others do not have a relation to. Can anyone think of a way that I can gather all of this data into one output sorted by the time stamp? Here's some example queries that would all need to be run and returned into one result set:

This is a query for finding people with one type of access:

select ul.log_id, ul.user_id, ul.patient_id, ul.log_type_id, ul.item_id, ul.comment, ul.log_time_stamp,
p.FirstName as PatientName, p.LastName as PatientLastName, u.firstname as userFirstname,
u.lastname as userLastname, u.username as username
from use_log ul INNER JOIN patients p ON
ul.patient_id = p.patientid INNER JOIN
Users u ON u.UserID= ul.user_id
where ul.user_id = 3
and p.owner <> 3
and log_type_id = 2
order by ul.log_time_stamp

This is a query for finding people with another type of access:

select ul.log_id, ul.user_id, ul.patient_id, ul.log_type_id, ul.item_id, ul.comment, ul.log_time_stamp,
p.FirstName as PatientName, p.LastName as PatientLastName, u.firstname as userFirstname,
u.lastname as userLastname, u.username as username
from use_log ul INNER JOIN patients p ON
ul.patient_id = p.patientid INNER JOIN
Users u ON u.UserID= ul.user_id
where ul.user_id <> 3
and p.owner = 3
and log_type_id = 2
order by ul.log_time_stamp

This is a query for finding out the information based on an action:

select ul.log_id, ul.user_id, ul.patient_id, ul.log_type_id, ul.item_id, ul.comment, ul.log_time_stamp,
p.FirstName as PatientName, p.LastName as PatientLastName, u.firstname as userFirstname,
u.lastname as userLastname, u.username as username
from use_log ul inner join patients p on p.patientid = ul.patient_id
inner join users u on u.userid = ul.user_id
inner join note n on n.note_id = ul.item_id
where log_type_id = 1 and ul.user_id <> 3
and n.recipient_id <>3 and n.user_id = 3
order by ul.log_time_stamp

Thanks in advance for any help!

 

[PauloRico]

If I understand correctly (which I don' think I do), why not use LEFT JOIN on the tables that have no relation. This will still select all the records in the main table, and will just return NULL fields for the other table.

 

[questhaven]

The reason why I dont think a left join will work is because the queries themselves are looking for different parameters. The top one requires:

where ul.user_id = 3
and p.owner <> 3
and log_type_id = 2

and the second one requires this:

where ul.user_id <> 3
and p.owner = 3
and log_type_id = 2

and the third one requires:
where log_type_id = 1 and ul.user_id <> 3
and n.recipient_id <>3 and n.user_id = 3

Is it even possible to get a result set of each of these queries together into a table so that they can be displayed arranged by timestamp?

 

[SonOfEmidec1100]

I think you will have probs trying to create one statement, I dont understand why the temp table idea does not work but then it is Friday night.
I would be use a stored proc like this

remove the order by from each query
SET NO COUNT ON
Make the first query a SELECT INTO #TEMP
Make the other 2 an INSERT INTO #TEMP
SET NOCOUNT OFF
SELECT * FROM #TEMP ORDER BY log_time_stamp
DROP TABLE #Temp

Or maybe try this

Remove the order by from the first 2 selects and replace with UNION ALL

 

[kutty007]

Yes
UNION ALL should work fine

==========================================

select ul.log_id, ul.user_id, ul.patient_id, ul.log_type_id, ul.log_type_id, ul.comment, ul.log_time_stamp,
p.FirstName as PatientName, p.LastName as PatientLastName, u.firstname as userFirstname,
u.lastname as userLastname, u.username as username
from use_log ul INNER JOIN patients p ON
ul.patient_id = p.patientid INNER JOIN
Users u ON u.UserID= ul.user_id
where ul.user_id = 3
and p.owner <> 3
and log_type_id = 2


UNION ALL


select ul.log_id, ul.user_id, ul.patient_id, ul.log_type_id, ul.item_id, ul.comment, ul.log_time_stamp,
p.FirstName as PatientName, p.LastName as PatientLastName, u.firstname as userFirstname,
u.lastname as userLastname, u.username as username
from use_log ul INNER JOIN patients p ON
ul.patient_id = p.patientid INNER JOIN
Users u ON u.UserID= ul.user_id
where ul.user_id <> 3
and p.owner = 3
and log_type_id = 2


UNION ALL


select ul.log_id, ul.user_id, ul.patient_id, ul.log_type_id, ul.item_id, ul.comment, ul.log_time_stamp,
p.FirstName as PatientName, p.LastName as PatientLastName, u.firstname as userFirstname,
u.lastname as userLastname, u.username as username
from use_log ul inner join patients p on p.patientid = ul.patient_id
inner join users u on u.userid = ul.user_id
inner join note n on n.note_id = ul.item_id
where log_type_id = 1 and ul.user_id <> 3
and n.recipient_id <>3 and n.user_id = 3