Conditional Joins - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional Joins

Author

Topic

IanKM
Starting Member

18 Posts

Posted - 2011-12-16 : 04:51:51

SELECT 'Driver' = CASE
WHEN s.vehicleid = '~C' THEN 'Cancelled'
WHEN s.vehicleid = '~R' THEN 'Refused'
WHEN s.vehicleid = '~U' THEN 'Unallocated'
ELSE d.drivername End
FROM Schedule s
LEFT OUTER JOIN drivers d ON s.vheicleid = d.driverid

Hi there,
I have this little query. which either displays a drivers name or The Word Above however the s.schedule id can either be numberic or not and the d.drivers id is always numeric. So when i do the join if there are some s.vehicleids that aren't numeric it brings up a conversion error.

basically i think that i only want to do the join if s.vehicleid is numeric but im open to suggestions

Many thanks Ian

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-16 : 05:53:15

Hi,

In the join condition datatype of columns should be same, If all the values in the s.vehicleids is numeric it will not throw error message. suppose If any of the value in the s.vehicleids column it will throw error. So change the table structure.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

IanKM
Starting Member

18 Posts

Posted - 2011-12-16 : 06:11:47

So i can't just say, only do the join if...

id have thought that seeing as im only asking for the d.drivername under certain conditions it wouldn't have done the join

Ian

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-16 : 06:19:54

Hi,

Have you 'driverid' column in Schedule table? If you have Schedule driverid then join with that. Can you explain more with table stucture with example data to resolve the probs?

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

IanKM
Starting Member

18 Posts

Posted - 2011-12-16 : 06:28:22

well the driverid in the schedule is the vehicleid but if the trip has been put in to cancelled the vehicleid will be '~C' meaning that it can't be joined with driverid. Unfortunatly i can't change the data type of the DriverId. i don't own the database i just try and support it

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-16 : 06:36:27

You generally don't want to do stuff like that - it indicates that you are overloading the column (making it do more than one thing)

However, you can use CASE to do what you wanted though.


SELECT 'Driver' = CASE 
WHEN s.vehicleid = '~C' THEN 'Cancelled'
WHEN s.vehicleid = '~R' THEN 'Refused'
WHEN s.vehicleid = '~U' THEN 'Unallocated'
ELSE d.drivername End
FROM Schedule s
LEFT OUTER JOIN drivers AS d ON
    d.[driverid] = CASE WHEN ISNUMERIC(s.[vehicleid]) = 1 THEN s.[vehicleid] ELSE NULL END

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-16 : 06:37:32

ISNUMERIC is a bit flaky so if you know the only possible formats then you may be better off using a NOT LIKE statement in place of ISNUMERIC.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 06:49:54

see an enhanced one here

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-16 : 06:55:41

quote:
Originally posted by IanKM

well the driverid in the schedule is the vehicleid but if the trip has been put in to cancelled the vehicleid will be '~C' meaning that it can't be joined with driverid. Unfortunatly i can't change the data type of the DriverId. i don't own the database i just try and support it

Ok can able add a new column to maintain a status (~C, ~R) and then dont store status values in vehicleid.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

IanKM
Starting Member

18 Posts

Posted - 2011-12-16 : 07:06:35

Hi Transact Charlie

thank you SOOOOOOOOO much that worked an absolute Treat!! However you did mix up the the joiny bit but i got there in the end

Thanks

Ian

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-16 : 07:11:30

quote:
Originally posted by IanKM

Hi Transact Charlie
thank you SOOOOOOOOO much that worked an absolute Treat!! However you did mix up the the joiny bit but i got there in the end

Cool, glad to help.

Sorry, can you post the correction? I don't see where I went wrong.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources