You need two JOINS in your query - one to join Company to AddressLink, and one to join AddressLink to Address. In your select list, you only need to...

Anthony

You'd need to create a trigger on your Episodes table that would roll back any INSERT operation that didn't comply.

John

SELECT MyColumn FROM MyTable

WHERE RIGHT(MyColumn, 1) = ' '

John

Karthik

Way 1 isn't syntactically correct, so you'd have to go with Way 2. But I think Jason's way is better still.

Also can you tell me why we haven't use...

Art

That's why I said to search this site - there are some ingenious ways of generating the numbers/dates if you look for them!

John

I've just seen another question answered something like this:

select TOP 100 * from table1 where

...

Karthik

First off, if @abc is varchar, then -1 isn't a valid value, although '-1' is.

Second, in the code snippet you posted, you were only using @abc as a means to...

As I said, if you can't change your function to a stored procedure then you're going to have to change the design of your database. Perhaps you could create...

Art

The best way is to have a separate "numbers table" or "tally table", and to join to that. They have a multitude of uses and usually come out on...

...

LEFT OUTER JOIN sys.types types

ON ...

Karthik

What is the data type of @abc?

John

The first part of your WHERE clause is effectively turning your second OUTER JOIN into an INNER JOIN. Try putting this condition as a join predicate instead.

John

OK, well you will have to replace the "..." in the snippet Gail gave to get you started with some actual code. Don't forget ROW_NUMBER always has a "()"...

You could combine the two, something like this:

SELECT

FROM Address a JOIN Publication p

ON LEFT(a.Addr_PostCode, CHARINDEX(' ',a.Addr_Postcode)-1)

= LEFT(p.Pub_PostCode, CHARINDEX(' ',p.Pub_Postcode)-1)

WHERE p.Pub_PublicationID =

John

OK, now please show us what you expect the query to return, and what you've tried so far.

John