Case in Inner Join? SQL statement help!!

Hi,

I have a 2 different telephone number tables,

Table 1 has some numbers beginning with '0' i.e. 08001234567 and some without the '0' i.e 8001234567

on my Table2 i only have numbers starting with '0' i.e 08001234567.

I would like to make a INNER JOIN statement and check if the telephone number dont have a starting '0', then append to it and try to do the join so I get both set of data.

for example (this doesnt work however...):

SELECT *

FROM dbo.Calls INNER JOIN
ON

CASE WHEN SUBSTRING(dbo.CallData.TelephoneNumber, 0, 1) = '0'

THEN dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber

ELSE '0' & dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber <--append a 0 at the start

END CASE

AND dbo.Products.FK_Client = dbo.CallDataSets.FK_Client
GROUP BY dbo.CallDataSets.FK_Client

I hope you understand what I am trying to achieve here...

Any help is appreciated!

Many thanks,

Jon

Try something like this:

Code Snippet

SELECT *
FROM dbo.Calls c
JOIN ...
ON ...
JOIN Post p
ON ( right(( '0' + c.TelephoneNumber ), 11 )) = ( right(( '0' + p.TelephoneNumber ), 11 ))
JOIN Products pr
ON ...

You only need to add the leading zero on the table that requires it -both are used above for illustration.

|||

thanks for the reply.

but the reason i need the CASE STATEMENT is that some of them don't have Zeros, and it wont join the table.

I want both numbers to be join with.

many thanks.

|||

So, did you even try the suggestion I sent.

If you did, what happened?

I have a 2 different telephone number tables,

Table 1 has some numbers beginning with '0' i.e. 08001234567 and some without the '0' i.e 8001234567

on my Table2 i only have numbers starting with '0' i.e 08001234567.

And of course, you could choose to correct the data that doesn't have a leading zero with an UPDATE statement like this (then both tables would have data in the same form):

Code Snippet

UPDATE Table1
SET TelephoneNumber = ( '0' + TelephoneNumber )
WHERE len( TelephoneNumber ) = 10

(or)

...
WHERE left( TelephoneNumber, 1 ) <> '0'

|||

thanks!

in the end is use CAST(object as bigint) to get rid or the zero.

|||That would also work just as well.