problem with joined tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem with joined tables

Author

Topic

MirandaJ
Starting Member

35 Posts

Posted - 2008-02-01 : 12:27:33

I posted Wednesday thinking a SELECT Distinct would solve my problem but it didn't. I have a stored procedure that is used to grab data from 4 tables that I need to join.
The 1st table (Application) holds a job applicant's name and some other data
The 2nd table (Jobs) holds the Job name and test type
The 3rd table (Locations) holds the locations
Then there is a foreign key many to many table (Application_Locations) that holds the applicants UserID and a LocationID. This table may have multiple rows for the same applicant with different locations in each row.

When the procedure is ran I want all the data that I am requesting from the Application table, and all the data that I am requesting from the Jobs table but only the 1st returned result of the Join on the Locations and Application_Locations table. What do I need to do to correct this so that I only display 1 row for each UserID no matter how many locations thay may have applied to. (You will notice that there are some IF statements so only the 2nd and 4th queries in the sproc are the ones that apply )

Here is the SPROC that is currently in place but is displaying a row for each location.


CREATE PROCEDURE sp_AdminListApplicants

@LocationID int,
@FolderID smallint,
@JobID int,
@SortOrder char(1)

AS


IF @JobID <> 9999
BEGIN		
	IF @LocationID <> 9999
	BEGIN
		SELECT  
			A.UserID, 
			A.Completed, 
			A.FolderID, 
			A.AppDateTimeStart, 
			A.ResumeFileName, 
			A.FirstName, 
			A.LastName, 
			A.PrescreenScore, 
			A.JobID, 
			A.ViewPre, 
			A.ViewApp, 
			A.ViewReport, 
			A.ViewResume, 
			J.JobTitle,
			J.TestType,
			L.BranchAbbreviation,
			AL.LocationID
		FROM 
			Locations L 
			INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
			INNER JOIN Application A ON AL.UserID = A.UserID
			INNER JOIN Jobs J ON J.JobID = A.JobID  
		WHERE 
			AL.LocationID= @LocationID 
			AND A.FolderID= @FolderID
			AND A.JobID = @JobID
		ORDER BY
			CASE
				WHEN @SortOrder = '4' THEN A.AppDateTimeStart 
			END DESC,
			CASE
				WHEN @SortOrder = '6' THEN A.PreScreenScore 
			END DESC,
			CASE
				WHEN @SortOrder = '2' THEN A.LastName  
			END DESC,
			CASE
				WHEN @SortOrder = '5' THEN A.PreScreenScore 		
			END ASC,
			CASE
				WHEN @SortOrder = '3' THEN A.AppDateTimeStart 					
			END ASC,
			CASE
				WHEN @SortOrder = '1' THEN A.LastName 		
			END ASC
	END
	
	ELSE
	BEGIN
		SELECT 
			A.UserID, 
			A.Completed, 
			A.FolderID, 
			A.AppDateTimeStart, 
			A.ResumeFileName, 
			A.FirstName, 
			A.LastName, 
			A.PrescreenScore, 
			A.JobID, 
			A.ViewPre, 
			A.ViewApp, 
			A.ViewReport, 
			A.ViewResume, 
			J.JobTitle,
			J.TestType,
			L.BranchAbbreviation,
			AL.LocationID
		FROM 
			Locations L 
			INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
			INNER JOIN Application A ON AL.UserID = A.UserID
			INNER JOIN Jobs J ON J.JobID = A.JobID  
		WHERE 
			A.FolderID= @FolderID
			AND A.JobID = @JobID
		ORDER BY
			CASE
				WHEN @SortOrder = '4' THEN A.AppDateTimeStart 
			END DESC,
			CASE
				WHEN @SortOrder = '6' THEN A.PreScreenScore 
			END DESC,
			CASE
				WHEN @SortOrder = '2' THEN A.LastName  
			END DESC,
			CASE
				WHEN @SortOrder = '5' THEN A.PreScreenScore 		
			END ASC,
			CASE
				WHEN @SortOrder = '3' THEN A.AppDateTimeStart 					
			END ASC,
			CASE
				WHEN @SortOrder = '1' THEN A.LastName 		
			END ASC
	END
END
	
ELSE
BEGIN
	IF @LocationID <> 9999
	BEGIN
		SELECT 
			A.UserID, 
			A.Completed, 
			A.FolderID, 
			A.AppDateTimeStart, 
			A.ResumeFileName, 
			A.FirstName, 
			A.LastName, 
			A.PrescreenScore, 
			A.JobID, 
			A.ViewPre, 
			A.ViewApp, 
			A.ViewReport, 
			A.ViewResume, 
			J.JobTitle,
			J.TestType,
			L.BranchAbbreviation,
			AL.LocationID
		FROM 
			Locations L 
			INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
			INNER JOIN Application A ON AL.UserID = A.UserID
			INNER JOIN Jobs J ON J.JobID = A.JobID  
		WHERE 
			AL.LocationID= @LocationID 
			AND A.FolderID= @FolderID
		ORDER BY
			CASE
				WHEN @SortOrder = '4' THEN A.AppDateTimeStart 
			END DESC,
			CASE
				WHEN @SortOrder = '6' THEN A.PreScreenScore 
			END DESC,
			CASE
				WHEN @SortOrder = '2' THEN A.LastName  
			END DESC,
			CASE
				WHEN @SortOrder = '5' THEN A.PreScreenScore 		
			END ASC,
			CASE
				WHEN @SortOrder = '3' THEN A.AppDateTimeStart 					
			END ASC,
			CASE
				WHEN @SortOrder = '1' THEN A.LastName 		
			END ASC
	END
	
	ELSE
	BEGIN
		SELECT 
			A.UserID, 
			A.Completed, 
			A.FolderID, 
			A.AppDateTimeStart, 
			A.ResumeFileName, 
			A.FirstName, 
			A.LastName, 
			A.PrescreenScore, 
			A.JobID, 
			A.ViewPre, 
			A.ViewApp, 
			A.ViewReport, 
			A.ViewResume, 
			J.JobTitle,
			J.TestType,
			L.BranchAbbreviation,
			AL.LocationID
		FROM 
			Locations L 
			INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
			INNER JOIN Application A ON AL.UserID = A.UserID
			INNER JOIN Jobs J ON J.JobID = A.JobID  
		WHERE 
			A.FolderID= @FolderID
		ORDER BY
			CASE
				WHEN @SortOrder = '4' THEN A.AppDateTimeStart 
			END DESC,
			CASE
				WHEN @SortOrder = '6' THEN A.PreScreenScore 
			END DESC,
			CASE
				WHEN @SortOrder = '2' THEN A.LastName  
			END DESC,
			CASE
				WHEN @SortOrder = '5' THEN A.PreScreenScore 		
			END ASC,
			CASE
				WHEN @SortOrder = '3' THEN A.AppDateTimeStart 					
			END ASC,
			CASE
				WHEN @SortOrder = '1' THEN A.LastName 		
			END ASC
	END
END
GO

Miranda

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 12:44:12

You have lots of redundant code here. You dont want IF ..ESLE i guess
you need only these. try & see:-

SELECT 
A.UserID, 
A.Completed, 
A.FolderID, 
A.AppDateTimeStart, 
A.ResumeFileName, 
A.FirstName, 
A.LastName, 
A.PrescreenScore, 
A.JobID, 
A.ViewPre, 
A.ViewApp, 
A.ViewReport, 
A.ViewResume, 
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM	Jobs J
INNER JOIN Application A
ON J.JobID = A.JobID  
INNER JOIN (SELECT UserID,MIN(LocationID) AS LocationID
            FROM Application_Locations 
            GROUP BY UserID ) AL 
ON AL.UserID = A.UserID
INNER JOIN Locations L 
ON AL.LocationID = L.LocationID  
WHERE (AL.LocationID= @LocationID OR @LocationID =9999)
AND A.FolderID= @FolderID  
AND (A.JobID = @JobID OR @JobID =9999)
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart 
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore 
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName  
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore 		
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart 			  END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName 		
END ASC

MirandaJ
Starting Member

35 Posts

Posted - 2008-02-01 : 12:53:22

The redundancy is because the 9999 values for locations and folders don't exist these are used when the website user wants to view all of the locations or all of the folders

If they select all folders or all locations the value 9999 is passed so that then I can build the Query.

Your sample will break because there is no 9999 value for either of these in the database. If you will look at the code again you will see that when it is 9999 for either of them then that parameter is not used in the where clause.

Miranda

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 12:58:34

quote:
Originally posted by MirandaJ

The redundancy is because the 9999 values for locations and folders don't exist these are used when the website user wants to view all of the locations or all of the folders

If they select all folders or all locations the value 9999 is passed so that then I can build the Query.

Your sample will break because there is no 9999 value for either of these in the database. If you will look at the code again you will see that when it is 9999 for either of them then that parameter is not used in the where clause.

Miranda

thats exactly what i'm achieving by this

WHERE (AL.LocationID= @LocationID OR @LocationID =9999)
AND A.FolderID= @FolderID
AND (A.JobID = @JobID OR @JobID =9999)

when @LocationID =9999 it bypasses the other condition AL.LocationID= @LocationID (thats what OR does) in WHERE clause.
similarly for JobID. and i think that was really what you were trying to achieve by your IF..ELSE above.

MirandaJ
Starting Member

35 Posts

Posted - 2008-02-01 : 13:07:19

You are 100% correct. I spoke too soon. I apologize for my rudeness.

Your solution works like a charm. Thank you!!!

Miranda

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 13:09:47

Thats ok. Feel free to come back whenever you face any problem. No worries. Cheers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources