Simple SQL Statement Issues

[Vogel515]

SELECT DISTINCT b.[Employee ID], b.Project, b.Product, b.[Project Salary], b.Report

FROM tbl_CHGLI030b AS b

WHERE b.Report<>"exclude";

Pretty straight forward right?

when I make it ="exclude" it returns all those where the Report field contains exclude, when I leave it as it is above, it returns nothing - and there are plenty that do not contain exclude.

Where NOT b.Report="exclude" is not working either... it is returning nothing, why is this?

 

[Vogel515]

grr found an answer... just did where b.report is null

Why didn't my code work before though?

 

[LuckyTaxi]

silly question but did you really write "b.Report<>"exclude";"

I thought u would need a space "b.Report <> "exclude";"

Other than that it looks fine to me.

 

[Vogel515]

No space, and == didn't work either

 

[LuckyTaxi]

see above, sorry im stupid i had to read it again

 

[Vogel515]

Tried that too, no luck.

It's SQL in access so its a bit messed up.

 

[LuckyTaxi]

Wait i think i got it. I ran a query against one of my table.

I did "select * from <table> where field_name <> "sam";

I have 5 rows in the table. 3 have NO VALUES, 1 has a value of "sam" and the last one is assigned NULL.

When I ran the sql statement above, I received 3 results (the 3 fields with no values). Notice how it DIDN'T pull the row with NULL value.
I assume your records are assigned NULL, which explains why it didnt match any rows and why when you did "is NULL" it returned all the rows w/o "exclude."

 

[Vogel515]

Ahh good call, I will include an or statement in case something that isn't exclude makes it into those fields.

Thank you for your help! I am in the process of learning

 

[LuckyTaxi]

SELECT DISTINCT b.[Employee ID], b.Project, b.Product, b.[Project Salary], b.Report FROM tbl_CHGLI030b AS b WHERE b.Report <> "exclude" OR b.Report is NULL;

 

[Vogel515]

Why "and" not "or"?

Edit: I could have sworn I read AND... but you can ignore this

 

[LuckyTaxi]

no my dumbass wrote it and then realized the mistake and changed it! just like my first reply in the post. i couldnt make up my mind!!!

good luck!

 

[Vogel515]

It is a friday afternoon, these mistakes are completely acceptable.

One last question for you: Can you suggest any good books? I use google religiously but I do like to have a reference at hand... I'm a big fan of the O'Reilly books, but I'm open to suggestions.

Edit: by a big fan, I mean it was the last/only programming book I bought and it works

 

[LuckyTaxi]

Books in regards to access or sql in general?

I can't speak for access, but for sql, there's a book and I dont know what it's call at the moment. I do know it has a red/white cover. Lots of sql books they make (i.e. "SQL in a Nutshell")
Maybe it is O'Reilly but I think of cyan when I think of O'Reilly's

 

[Vogel515]

Sorry for the delay in response... I tend not to get near my computer on the weekends.

I was looking for books in regards to SQL, I think you are referring to a O'Reilly book, the one I currently have is named "web development in a nutshell" and it is also Cyan + White.