CODEWITHSUNDEEP
t-sqllogic
GregD
GregD

Reputation: 7000

How to shorten long UNION ALL query in TSQL

I need to shorten this query and while I'm pretty good at SQL, I'm still learning.

    SELECT
        'doejoh',
        DATETIME,
        [Recipient-Address], [Message-Subject], [Sender-Address] 
    FROM
        dbo.Logs 
    WHERE
        LEFT([Recipient-Address], 6) IN ('doejoh') 
UNION ALL 
    SELECT
        'doejoh',
        DATETIME,
        [Recipient-Address], [Message-Subject], [Sender-Address] 
    FROM
        dbo.Logs 
    WHERE
        LEFT([Recipient-Address], 10) IN ('john.doe@g') 
UNION ALL 
    SELECT
        'doejoh',
        DATETIME,
        [Recipient-Address], [Message-Subject], [Sender-Address] 
    FROM
        dbo.Logs 
    WHERE
            LEFT([Sender-Address], 6) IN ('doejoh') 
    UNION ALL 
    SELECT
        'doejoh',
        DATETIME,
        [Recipient-Address], [Message-Subject], [Sender-Address] 
    FROM
        dbo.Logs 
    WHERE
            LEFT([Sender-Address], 10) IN ('john.doe@g')
    ORDER BY
        DateTime

I have to use this union, because in the same table, there are 4 different possibilities for each user and their email address. That being said, I have 30 users, so 30x4 would be 120 groups in this entire query. The reason the first column has to be the username is because I'm using that column in a Crystal Report.

I'm just looking to create some logic for my query that will shorten it down, while at the same time, "assigning" each user to their appropriate first column.

Edited to add

While this will shorten my query, I'll still have to have 30 unions:

SELECT
   'doejoh',
   DATETIME,
   [Recipient-Address], [Message-Subject], [Sender-Address] 
FROM
   dbo.Logs 
WHERE
   LEFT([Recipient-Address], 6) IN ('doejoh') OR
   LEFT([Recipient-Address], 10) IN ('john.doe@g') OR
   LEFT([Sender-Address], 6) IN ('doejoh') OR
   LEFT([Sender-Address], 10) IN ('john.doe@g')
ORDER BY
   DateTime

Because the next user would be unioned to the previous one:

UNION ALL 
SELECT
   'doejan',
   DATETIME,
   [Recipient-Address], [Message-Subject], [Sender-Address] 
FROM
   dbo.Logs 
WHERE
   LEFT([Recipient-Address], 6) IN ('doejan') OR
   LEFT([Recipient-Address], 10) IN ('jane.doe@g') OR
   LEFT([Sender-Address], 6) IN ('doejan') OR
   LEFT([Sender-Address], 10) IN ('jan.doe@g')

And so on and so forth... any shorter way?

Upvotes: 1

Views: 542

Answers (5)

Robin Day
Robin Day

Reputation: 102478

Is there a reason something like this won't work?

CREATE TABLE #TempNames
(
    shortname nvarchar(6),
    longname nvarchar(10)
)

INSERT INTO #TempNames (shortname, longname) VALUES('doejoh', 'john.doe@g')
INSERT INTO #TempNames (shortname, longname) VALUES('doejan', 'jan.doe@g')
INSERT INTO #TempNames (shortname, longname) VALUES('smibob', 'bob.smith@g')

SELECT
    #TempName.shortname,
    DATETIME,
    [Recipient-Address], [Message-Subject], [Sender-Address]
FROM
    dbo.Logs
INNER JOIN
    #TempNames
ON
    LEFT([Recipient-Address], 6) = #TempNames.shortname
OR
    LEFT([Recipient-Address], 10) = #TempNames.longname
OR
    LEFT([Sender-Address], 6) = #TempNames.shortname
OR
    LEFT([Sender-Address], 10) = #TempNames.longname

Upvotes: 4

DMCS
DMCS

Reputation: 31870

Create a table with the email address of the 30 people. Table: Emails Columns: short6, long10, email

then using only 1 union all

Select Emails.short6, Logs.DateTime, Logs.[Recipient-Address], Logs.[Message-Subject], Logs.[Sender-Address]
From Emails JOIN Log on Emails.email = Log.[Recipient-Address]
Where LEFT([Recipient-Address], 6) = Emails.short6 
or LEFT([Recipient-Address], 10) = Emails.long10

union all

Select Emails.short6, Logs.DateTime, Logs.[Recipient-Address], Logs.[Message-Subject], Logs.[Sender-Address]
From Emails JOIN Log on Emails.email = Log.[Sender-Address]
Where LEFT([Sender-Address], 6) = Emails.short6 
or LEFT([Sender-Address], 10) = Emails.long10

Upvotes: 1

Sam Saffron
Sam Saffron

Reputation: 131112

create a mapping table and join to it.

eg. something like 

select user_name, DateTime .... 
from Logs
join Users on 
   LEFT([Recipient-Address], 6) IN (user_name) OR
   LEFT([Recipient-Address], 10) IN (user_email) OR
   LEFT([Sender-Address], 6) IN (user_name) OR
   LEFT([Sender-Address], 10) IN (user_email)

Upvotes: 2

James Curran
James Curran

Reputation: 103515

Can't you use just...

SELECT
    'doejoh',
    DATETIME,
    [Recipient-Address], [Message-Subject], [Sender-Address] 
FROM
    dbo.Logs 
 WHERE
    (LEFT([Recipient-Address], 10) IN ('john.doe@g'))
or  (LEFT([Recipient-Address], 6) IN ('doejoh') )
or  ( LEFT([Sender-Address], 10) IN ('john.doe@g'))
or  (LEFT([Sender-Address], 6) IN ('doejoh') )

Upvotes: 1

marc_s
marc_s

Reputation: 754558

You should rewrite your query as:

SELECT
   'doejoh',
   DATETIME,
   [Recipient-Address], [Message-Subject], [Sender-Address] 
FROM
   dbo.Logs 
WHERE
   LEFT([Recipient-Address], 6) IN ('doejoh') OR
   LEFT([Recipient-Address], 10) IN ('john.doe@g') OR
   LEFT([Sender-Address], 6) IN ('doejoh') OR
   LEFT([Sender-Address], 10) IN ('john.doe@g')
ORDER BY
   DateTime

SHould be the same in terms of selection, just a bit faster and easier to understand, I think.

Marc

Upvotes: 5

Related Questions