Select Statement returning wrong results.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Statement returning wrong results.

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-06-06 : 08:59:00

Hi There,
Can you help please.
I have a select statement that uses about five tables which works perfect if I do not use a where statement. I need to use a where statement to show only records that do not have a certain value in an emailhistory table. There are many records in the tbEmailHistory table with the same linking id. The select statement below only returns records that do not have anything in the tbEmailHistory table which is wrong. I want to show all records that does not have EmailType of '(2) Accept'. If there are five records with the same MainId as long as there is not an EmailType of '(2) Accept' then show them, if it does have this EmailType show none of them. This is my Select Statement:

SELECT
tbMainTable.MainId,
tbMainTable.Description,
tbMainTable.rUserId,
tbUsers.LastName + ', ' + tbUsers.FirstName As RPFullName,
tbMainTable.aUserId,
tbUsers1.LastName + ', ' + tbUsers1.FirstName As APFullName,
tbMainTable.TypeId,
tbTypes.TypeDescription,
tbMainTable.Risk,
tbPriority.Description As RiskDescription,
tbMainTable.DateRaised,
tbMainTable.PlannedStartDate,
tbMainTable.ActualStartDate,
tbMainTable.PlannedCompletionDate,
tbMainTable.ActualCompletionDate,
tbMainTable.EditorComments,
tbMainTable.EditorActions,
tbMainTable.SourceId,
tbSource.SourceDescription,
tbMainTable.PerComplete,
tbMainTable.LocationId,
tbUserLoc.Location
From
tbMainTable Inner Join
tbUsers On tbMainTable.rUserId = tbUsers.PersonnelId Inner Join
tbUsers tbUsers1 On tbMainTable.aUserId = tbUsers1.PersonnelId Inner Join
tbTypes On tbMainTable.TypeId = tbTypes.TypeId Inner Join
tbPriority On tbMainTable.Risk = tbPriority.RiskId Inner Join
tbSource On tbMainTable.SourceId = tbSource.SourceId Inner Join
tbUserLoc On tbMainTable.LocationId = tbUserLoc.LocationId
WHERE NOT EXISTS (
SELECT *
FROM tbEmailHistory
WHERE(tbEmailHistory.MainId = tbMainTable.MainId)
AND tbEmailHistory.EmailType <> '(2) Accept'
)
Order by tbMainTable.MainId

Thanks for any help you can give.

Best Regards,

Steve

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-06 : 09:29:00

Based on your description, it seems like you need to change the <> to = as in:


....
WHERE NOT EXISTS ( 
SELECT * 
FROM tbEmailHistory 
WHERE(tbEmailHistory.MainId = tbMainTable.MainId) 
AND tbEmailHistory.EmailType = '(2) Accept' 
) 
....

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-06-07 : 03:48:26

Hi Sunita,
Thanks for the reply and your help once again.

I have changed the <> to a = but it still does not work.
The query is only returning results if there is no matching MainId in the tbEmailHistory table.

Thanks again.

Steve

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-07 : 08:34:48

If that didn't work, I may not have understood the requirements correctly. See the example below - the query that I posted earlier is designed to pick up MainIds 1 and 2 in the example below, but not MainIds = 3. That is because for MainIds =3, there is one row in the #tmpMainTable where EmailType is '(2) Accept'.

If that is not what you are looking for, can you post some sample data, perhaps by modifying the sample code given below?


CREATE TABLE #tmpMainTable (MainId INT);
CREATE TABLE #tmpEmailHistory (MainId INT, EmailType VARCHAR(255));

INSERT INTO #tmpMainTable 
SELECT 1 UNION SELECT 2 UNION SELECT 3;

INSERT INTO #tmpEmailHistory
SELECT 1,'a' UNION select 1,'b' UNION 
SELECT 2, 'x' UNION SELECT 2, '(2) Accept' UNION 
SELECT 3, 'y' UNION SELECT 3,'z';

SELECT
	MainId
FROM
	#tmpMainTable
WHERE
	NOT EXISTS
	(
		SELECT * 
		FROM #tmpEmailHistory
		WHERE
			#tmpMainTable.MainId = #tmpEmailHistory.MainId
			AND #tmpEmailHistory.EmailType = '(2) Accept'
	);

DROP TABLE #tmpMainTable;
DROP TABLE #tmpEmailHistory;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources