How to get rid of UNION from this query

Question

Post reply

How to get rid of UNION from this query

ramadesai108

SSChampion

Points: 10609
More actions
July 26, 2010 at 12:33 pm

#224626

Hi All,
How do I get rid of the Union from the following query:
select
SUBSTRING(name, CHARINDEX(',', name) + 1, 25) as UserFirstName,
SUBSTRING(name, 1, CHARINDEX(',', name) - 1) as UserLastName,
user_id as UserIdIssued,
'N/A' as Division,
'N/A' as DivAdmin,
right('0' + rtrim(month(insert_dt)), 2) + '-' + right('0' + rtrim(day(insert_dt)), 2) + '-' + ltrim(str(year(insert_dt))) as CreatedDate,
ltrim(str(datepart(hour,insert_dt))) + ':' + ltrim(str(datepart(minute,insert_dt))) as CreatedTime,
insrt_user as CreatedBy
from USERS
where isnull(admn_ind, 'N') = 'Y' or isnull(SysAdminInd,'N') = 'Y'
union all
select
SUBSTRING(name, CHARINDEX(',', name) + 1, 25) as UserFirstName,
SUBSTRING(name, 1, CHARINDEX(',', name) - 1) as UserLastName,
table1.user_id as UserIdIssued,
table2.div_name as Division,
table1.admn_ind as DivAdmin,
right('0' + rtrim(month(USERS.insert_dt)), 2) + '-' + right('0' + rtrim(day(USERS.insert_dt)), 2) + '-' + ltrim(str(year(USERS.insert_dt))) as CreatedDate,
ltrim(str(datepart(hour,USERS.insert_dt))) + ':' + ltrim(str(datepart(minute,USERS.insert_dt))) as CreatedTime,
USERS.insrt_user as CreatedBy
from USERS inner join table1
on USERS.user_id = table1.user_id
inner join table2
on table1.div_id = table2.div_id
order by UserLastName, UserFirstName

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jvanderberg SSCertifiable Points: 6762 More actions · Answer 1

FROM USERS

LEFT JOIN table1 ON USERS.user_id = table1.user_id

LEFT JOIN table2 ON table1.div_id = table2.div_id

WHERE isnull(admn_ind, 'N') = 'Y' OR isnull(SysAdminInd,'N') = 'Y'

OR (table1.user_id IS NOT NULL AND table2.div_id IS NOT NULL)

Change you joins to LEFT joins and add an OR to the WHERE clause, checking to make sure that there are values in the key columns of table1 and table2.

ramadesai108 SSChampion Points: 10609 More actions · Answer 2

Hello jvanderberg,

Thank you very much. That worked. Thanks for your time.

PavanKK Old Hand Points: 375 More actions · Answer 3

In select statement You can use

CONVERT(VARCHAR,insert_dt,110)AS CreatedDate

to get the same date format as you expected from following

right('0' + rtrim(month(insert_dt)), 2) + '-' + right('0' + rtrim(day(insert_dt)), 2) + '-' + ltrim(str(year(insert_dt))) as CreatedDate

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 4

Can i know what for you are using the right() function..??

[font="Comic Sans MS"]Praveen Goud[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Praveen Goud Kotha (7/28/2010)
Can i know what for you are using the right() function..??

SELECT [Month] = RIGHT('0' + '1', 2) UNION ALL

SELECT RIGHT('0' + '2', 2) UNION ALL

SELECT RIGHT('0' + '3', 2) UNION ALL

SELECT RIGHT('0' + '4', 2) UNION ALL

SELECT RIGHT('0' + '5', 2) UNION ALL

SELECT RIGHT('0' + '6', 2) UNION ALL

SELECT RIGHT('0' + '7', 2) UNION ALL

SELECT RIGHT('0' + '8', 2) UNION ALL

SELECT RIGHT('0' + '9', 2) UNION ALL

SELECT RIGHT('0' + '10', 2) UNION ALL

SELECT RIGHT('0' + '11', 2) UNION ALL

SELECT RIGHT('0' + '12', 2)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 6

if any one asks me to write the same above code,

i will prefer the following way..

SELECT REPLICATE(0,LEN(DATEPART(M,GETDATE())))+CONVERT(CHAR(2),DATEPART(M,GETDATE()))

Can any one say which will be the optimized way to follow...??

[font="Comic Sans MS"]Praveen Goud[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Praveen Goud Kotha (7/28/2010)
if any one asks me to write the same above code,
i will prefer the following way..
SELECT REPLICATE(0,LEN(DATEPART(M,GETDATE())))+CONVERT(CHAR(2),DATEPART(M,GETDATE()))
Can any one say which will be the optimized way to follow...??

Not without testing, but:

SELECT REPLICATE(0, LEN(DATEPART(M, GETDATE()))) + CONVERT(CHAR(2), DATEPART(M, GETDATE())) -- long-winded

-- or

SELECT RIGHT('0' + RTRIM(MONTH(GETDATE())), 2) -- includes implicit conversion

-- or

SELECT REPLACE(STR(MONTH(GETDATE()), 2), ' ', '0') -- no implicit conversion

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden