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.
| Author |
Topic |
|
interclubs
Yak Posting Veteran
63 Posts |
 Posted - 2004-02-25 : 19:43:07
|
| I have a select statement with lots of parameters for the data i need to retrieve...But all the statements are not being evaluated by sql server... its selecting all the data, and ignoring the email is not null syntax as well as some others.'..... WHERE (WeddingID = 9) AND (InviteGroup IN (1, 3, 4, 2, 5)) AND (Email <> '') AND (Email is not null) AND (Complete = 'N') OR (Address = '') OR (Address IS NULL) OR (City IS NULL) OR (State IS NULL) OR (Zip IS NULL) OR (City = '') OR (State = '') OR (Zip = '')'I know there is some syntax mistake i made in there, but I can't for the life figure it out.... |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-25 : 22:00:23
|
| Can you send the entire select? I'm thinking just from looking at it that you need to use parans around groupings of the AND and OR statements to force it to evaluate in the correct order. What you are basically saying per table is evaluate my AND statements for each join, then evaluate my OR statements for each join. Therefore, if the Email is not null, but the Address in the same table is null, the address will trump the email. Remember the processing order for logic in SQL Server. You can find more by looking up "OR operators" and "AND operators" in SQL Books Online.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
interclubs
Yak Posting Veteran
63 Posts |
Posted - 2004-02-25 : 22:11:54
|
| Here it is. I didn't even know there was a processing order, which might be a part of the problem.SELECT Wedding_List.ID, Wedding_Guest.Email FROM Wedding_List RIGHT OUTER JOIN Wedding_Guest ON Wedding_List.ID = Wedding_Guest.InviteID WHERE Wedding_List.WeddingID = 9 AND Wedding_List.InviteGroup IN (1, 3, 4, 2, 5) AND Wedding_Guest.Email <> '' and Wedding_Guest.Email is not null AND Wedding_List.Complete = 'N'OR Wedding_List.Address = '' OR Wedding_List.Address IS NULL OR Wedding_List.City IS NULL OR Wedding_List.State IS NULL OR Wedding_List.Zip IS NULL OR Wedding_List.City = '' OR Wedding_List.State = '' OR Wedding_List.Zip = '' |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-25 : 22:45:54
|
| SELECT Wedding_List.ID, Wedding_Guest.Email FROM Wedding_List RIGHT OUTER JOIN Wedding_Guest ON Wedding_List.ID = Wedding_Guest.InviteID WHERE Wedding_List.WeddingID = 9 AND Wedding_List.InviteGroup IN (1, 3, 4, 2, 5) AND Wedding_Guest.Email <> '' AND Wedding_Guest.Email IS NOT NULL AND (Wedding_List.Complete = 'N' OR Wedding_List.Address = '' OR Wedding_List.Address IS NULL OR Wedding_List.City IS NULL OR Wedding_List.State IS NULL OR Wedding_List.Zip IS NULL OR Wedding_List.City = '' OR Wedding_List.State = '' OR Wedding_List.Zip = '')MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Select Syntax not getting parsed...