Simple Query Question

[Jason92]

I am having trouble with a simple query...

I am using the QBE grid that looks like this (for example):

column1 column2 column3
Animal Color firstName

I want to enter in the parameters in the grid to show me all animals except where animal = Dog and color = White.

I tried to enter in <>"Dog" and <>"White" in the parameters for the appropriate columns, but it does not show any color dog...

What am I missing?

 

[Melagan]

I figure the SQL for something like that should be:

SELECT t.column1, t.column2, t.column3, t.column2 & " " & t.column1 AS ColorDog
FROM yourtable as t
WHERE ColorDog <> "White Dog";

Otherwise <> "Dog" on column1 will exclude ALL dogs regardless of color, and <> "White" on column2 will exclude ALL white colored animals, regardless of animal type.

There may be a more elegant way of doing this, but I think the above code should work just fine.


~Melagan
______
"It's never too late to become what you might have been.

 

[lespaul]

you can't use an alias within the same query (ie ColorDog in the SELECT and WHERE clauses)

SELECT * FROM TABLE WHERE ANIMAL <> "DOG" AND COLOR <> "WHITE"

in the QBE Grid you would put DOG and WHITE on the SAME row:

column1  column2   column3
Animal   Color     firstName
<> "DOG"  <>"WHITE"

you get an OR combination if you put them on different rows:

column1  column2   column3
Animal   Color     firstName
<> "DOG"  
         <>"WHITE"

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Melagan]

Oh yes Leslie, thats right!

My revised SQL:

SELECT t.column1, t.column2, t.column3
FROM yourtable as t
WHERE t.column2 & " " & t.column1 <> "White Dog";

Note to self - test what you type if you're unsure about your own answer =P


~Melagan
______
"It's never too late to become what you might have been.