Prev: install sql server problem
Next: Attempt to fetch logical page in database
From: Mark B on 29 Jul 2010 01:19
Rather than use a UNION statement to add the outputs of these two SELECT statements, is there another way to combine the outputs via one SELECT statement (I only want unique values of tblLSKTransactions.CounterID)?


SELECT tblLSKTransactions.CounterID
FROM tblLSKTransactions
INNER JOIN tblLSKMembers
ON tblLSKTransactions.MemberEmail = tblLSKMembers.MemberEmailAddress
INNER JOIN tblLSKMembersTransactionViewers
ON tblLSKMembers.MemberEmailAddress = tblLSKMembersTransactionViewers.MemberEmailAddress
WHERE (tblLSKMembersTransactionViewers.TransactionViewerEmailAddress = N'test(a)test.com')

SELECT tblLSKTransactions.CounterID
FROM tblLSKTransactions
INNER JOIN tblLSKMembers
ON tblLSKTransactions.MemberEmail = tblLSKMembers.MemberEmailAddress
INNER JOIN tblLSKMembersTransactionViewersIDOnly
ON tblLSKTransactions.CounterID = tblLSKMembersTransactionViewersIDOnly.TransactionID
WHERE (tblLSKMembersTransactionViewersIDOnly.TransactionViewerEmailAddress = N'test(a)test.com')
From: Erland Sommarskog on 29 Jul 2010 08:02
Mark B (none123(a)none.com) writes:
> Rather than use a UNION statement to add the outputs of these two SELECT
> statements, is there another way to combine the outputs via one SELECT
> statement (I only want unique values of tblLSKTransactions.CounterID)?

This is a possibility:

SELECT T.CounterID
FROM tblLSKTransactions T
WHERE EXISTS (SELECT *
FROM tblLSKMembers
JOIN tblLSKMembersTransactionViewers MTV O
ON M.MemberEmailAddress = MTV.MemberEmailAddress
WHERE T.MemberEmail = M.MemberEmailAddress
AND MTV.TransactionViewerEmailAddress = N'test(a)test.com')
OR EXISTS (SELECT *
FROM tblLSKMembersTransactionViewersIDOnly MTVO
WHERE T.CounterID = MTVO.TransactionID
AND MTVO.TransactionViewerEmailAddress = N'test(a)test.com')


But I would be surprised if you report that the UNION query is faster.

Note hat the second of your queries can be simplified to:

SELECT T.CounterID
FROM tblLSKTransactions T
JOIN tblLSKMembersTransactionViewersIDOnly MTVO
ON T.CounterID = MTVO.TransactionID
WHERE MTVO.TransactionViewerEmailAddress = N'test(a)test.com'

There is, there no need to join to tblLSKMembers.


I've taken the liberty to introduce alias. I had to - else I was not
able to read the query.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Mark B on 30 Jul 2010 19:06
Thanks.
 | 
Pages: 1
Prev: install sql server problem
Next: Attempt to fetch logical page in database