Perform your joins in the FROM instead of the WHERE and use LEFT JOIN:

SELECT Roles.RoleDescription, Departments.DepartmentName

FROM Roles

INNER...

>>I can parse it out into my sproc using the field length

If the purpose of the spaces is simply for field-length based parsing, why not parse using the CharIndex()...

Normal SQL behaviour.

What were you expecting ? Use SELECT DISTINCT if you only want unique rows returned.

Both fair points.

Here's something interesting though. Run it a couple of times with the 1 cross-join. Now uncomment the other 8 joins to syscomments. See any meaningful change in io...

>> To get 100,000 rows from three cross joins, you need at least 47 rows in syscomments. A new database will have 0.

Hence why you run it in "master", where...

If a parent has no children, do you want the parent reported with zero for NumChildren, or excluded from the resultset completely ?

Derived table to assemble the ranking, outer query on the derived table to find the max rank for each group and sort on it:

Select ItemNumber, PL, Max(Rank)

From

(

  select rank,...

>>if a value in a column is a alphabetic value

How do you define "alphabetic value" ?

Just the upper & lowercase letters a to z ?

What about punctuation, periods, commas,...

Sorry, under-caffeinated moment. Didn't clue in that issue_main_id was the joining column. Fix it by grouping on just issue_main_id in the derived table.

SELECT distinct im.ISSUE_MAIN_ID, im.ISSUE_REF

FROM dbo.ISSUE_MAIN As im...

SELECT distinct im.ISSUE_MAIN_ID, im.ISSUE_REF

FROM dbo.ISSUE_MAIN As im

Inner Join

-- Join derived table that returns rows where all in the group are status 3

(

  Select

    issue_log_status_id

  From  issue_log

  Group...

Use a derived table to locate the duplicates. Join to the derived table to return the rest of the columns for those records.

Select t.*

From YourTable As t

Inner Join

(

  Select UserID,...

For generating large numbers of rows, I usually use the master database and just cross join syscomments with itself a few times:

use master

go

select top 100000 rand(checksum(newid()))

from syscomments as...

Agreed, 3 separate threads with 3 variations on essentially the same problem, all lacking various required details, is not the ideal way to go about getting a solution.

I stopped replying because...

Use Replace() to strip out the CRLF pairs prior to comparing.

Declare @CRLF As char(2)

-- Construct a CR/LF character pair

Select @CRLF = char(13) + Char(10)

Select *

From TableA

Inner Join TableB

  On...

Insufficient info.

What are the column or columns that uniquelty identify each #Tmp record and join it to the resultset of GE_Claim and GE_Reserve.