Where clause and Joins - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Where clause and Joins

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-12-17 : 10:30:25

Joining three tables.


SELECT hist.QtyID, u.Email
FROM Users u
INNER JOIN UserRoles ur ON ur.UserID = u.ID
INNER JOIN Roles r ON r.RoleID = ur.RoleID
LEFT OUTER JOIN RAHistory hist ON hist.UserID = u.ID
WHERE r.RoleName = 'Notifier'--@roleName
AND (hist.QtyID = 714 OR hist.QtyID IS NULL)

If the last AND is left off the results are as expected.
All rows are returned for all QtyID and for NULLs.
Keep the last AND statement and rows are missing.

First, all seven people are needed with the role Notifier.
In the RAHistory table, the userID is saved as well as dates, etc. when an email was sent.
The QtyID is also saved in this table.
But adding the QtyID parameter just does not seem to work quite right.

This sql also uses ROW_NUMBER() OVER (PARTITION BY.......
in order to get distinct rows, but that part is working fine, so I left it out to simplify the sql.

Other failed attempts are:
OR hist.ItemPriceQtyID = 714 -- the param is ignored
AND hist.ItemPriceQtyID = 714 -- only one row returned - need the rest of the roles (six more users)

Any suggestions are most welcomed.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-17 : 10:39:59

>>If the last AND is left off the results are as expected.
Not sure what the problem/question is. Why not just leave off the last AND?

Be One with the Optimizer
TG

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-12-17 : 10:42:59

The results are the rows returned contain all the QtyID values.
It needs to be narrowed to only the one QtyID value.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-17 : 10:53:21

try this:

AND (hist.QtyID = 714 OR hist.UserID IS NULL)

Be One with the Optimizer
TG

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-12-17 : 10:55:44

Yes, I have already tried that as the sql above states.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-17 : 10:57:48

the sql above has:
AND (hist.QtyID = 714 OR hist.QtyID IS NULL)

vs. mine:

AND (hist.QtyID = 714 OR hist.UserID IS NULL)

Be One with the Optimizer
TG

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-12-17 : 11:03:31

Yes, you're right. Your suggestion was different.
Guess I have too many things going at once.

But your suggestion did not work.
It returns only the users in the hist table and ignores the qtyID.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-17 : 11:19:34

Ok, then I'm not understanding the problem. Can you post a small example to illustrate the problem (Using executable DDL/DML) ?
Perhaps the row_number() logic you alluded to is messing something up. There is probably a better way to deal with your "duplicates" anyway.

Be One with the Optimizer
TG

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-12-17 : 11:37:52

Since this does not seem to want to cooperate, I went a different route.
This is an internal app so performance can be lost on this one very small sproc which binds a small gridview.

I created a temp table and used my original sql without the QtyID and inserted it into the temp table.
Then updated to null certain fields that <> the QtyID that was needed.
Then used ROW_NUMBER() OVER on the temp table to get the correct rows but had to order by the date desc.

It seems to work ok for now.

Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources