Conditions in Select

Anon

Can some one point me in the right direction for the following query:

I want to select all the columns from a table that are not null.

SELECT * FROM xxxx WHERE id='$id';

And have the * be all the columns which are not NULL.

Thanks

chowdary

Hi Damon,
Your question is a bit confusing.
Suppose you have this table

Users

id name tel mail
1 aaaa NULL abc@bc.com
2 bbbb 252525 ccc@cbc.com
3 dddd 343434 NULL
4 vxsg NULL NULL

Which rows you want to select

Cheers
Ajay

Anon

Users

id name tel mail
1 aaaa NULL abc@bc.com
2 bbbb 252525 ccc@cbc.com
3 dddd 343434 NULL
4 vxsg NULL NULL

If i was after id=4, i would only want the columns which are not null, so name.

If id=3 i would want it to return the name and tel fields only.

I wanted to do this so i could use count() in PHP. I think a faster way to do it, is to just check if it is NULL in PHP.

Is there a quick way in mySQl to get a total of all the columns in a row which are not null ie:
id=1 => 3
id=4 => 1

Anon

Please disregard any escape chars if present.
Here's your query.
select * from table where column !='';
Regards.
Kernel Panic

Anon

Let me expand on that query incase I mis understood the question.
select from table where column !='';
select all ()
from table
where column
is not (!)
equal to (=)
null ('')
terminate (😉

[deleted]

That doesn't work, because
NULL != ''

(yes, oracle seems to think that ''=NULL, but oracle is crap)

instead, try

SELECT column1, column2
FROM table
WHERE column1 IS NOT NULL

Anon

uhhhh........that's a mysql query.

[deleted]

uuuuuuuuhhh.... no, that's ANSI SQL.

Anon

hahahahahaha!!!!!!
whatever looser.

smarlowe

It certainly worked in Postgresql on my box.

smarlowe

i.e. "I know I'm wrong, and look like an idiot, but rather than admit that, I'll insult someone."

Maybe next time you can just admit it?

Anon

I think someone once said "a little knowledge is dangerous".
geeze man....That was lame.
"THAT DOESN'T WORK!! THAT SUCKS!! THAT'S PURE SQL!!" hahaha!!!!!!

Anon

Besides that......If you so called "WIZARDS" gave a @!#$ about actually helping anyone here, you'd spend some time giving a little instruction. I took time out of my busy day, to make an attempt at helping a guy that is having obvious problems. Who really gives a big flying fucking rat's ass, as to weather or not the syntax is ANSI92 compliant.
What do you do? Troll this board all day waiting on someone to dispence advice that may just happen to be mysql specific?
I say GET A FUCKING LIFE.
Besides, you don't know who the @!#$ I am or anything about me. BTW....I can say with certianty....neither of you wrote any books on MySQL or PHP.

Anon

Wow, so we provide a useful answer that works for mysql and postgres and any other ANSI 92 compliant database, and we get fragged from you? Where was your answer? Oh that's right, it's the one that doesn't work with any database I know if, since NULL != ''.

ANSI-92 versus ad-hoc, I don't really care, but vincent posted a query that worked, you posted one that didn't.

then you became abusive. Well, you're WRONG and won't admit it, plain and simple. If you can't take it, don't dish it out.

Look over this site for my and vincent's name. It's everywhere. We've answered literally hundreds of questions, with hundreds of corresponding "thanks you"s. And you've contributed how exactly?

Anon

Ok....you are right.
I guess I'm just in a flaming mood today.
Great work guys!
Hope we can keep this website going!
I got one question Scott. Are you sure that query didn't work, where \IS NOT NULL; did?
Hmmmmm?
You're right....NULL !='';
But ''= will always be =='-';
ok....I've got a weekend to tend too....have a good one guys!

[deleted]

/me pats Scott on the shoulder, "Well said Scott"

I rest my case.

Anon

Kernel Panic wrote:

I think someone once said "a little knowledge is dangerous".

Yes, ad evidenced by your incorrect answer and subsequent juvenile antics, I'd tend to agree. did you notice the original posting mentioned he was running MYSQL? Maybe that's why vincent used ANSI-92 SQL, since MYSQL at least tries to adhere to that standard, and using ANSI SQL ensures you can move your code from one dbms to another?

Of course, that would be more than a little knowledge, wouldn't it? Yes, that would take a bit of thought and consideration to see and realize that your answer was just wrong.

Nothing wrong with being wrong. I've been wrong many times here. I've admitted it. I try not to "kill the messenger" when I am. I just admit I'm wrong and move on.

vincent and I have gone around a few times. But we've never descended to the depths you have here, because we're both trying to do the same thing, help other people out and find the right answers.

You seem to just be swinging your dick around trying to hit people in the eye with it. ;⁾

Anon

Yes, I did test your !='' on my postgresql machine (and I'm sure vincent tested it on mysql) and it did not work.

is null and is not null do work though.

P.s. I get a sick feeling in my stomach at work when they try to make me move the "standard" of Oracle. Ugh, it really is about as far from portable as you can get.

Have a good weekend, and hopefully it's raining a lot less where everybody else is than over here in Denver.

Anon

hahahaa!!!! You guys ARE cool after all!
I apoligize....my first flame was reactionary.
But.....don't tell me I'm wrong. I know better. I did indeed make the mistake of assuming he was using MySQL.
Inside MySQL, that statement is perfect.
BTW.....I hate Larry too!

Anon

I agree Brother Panic.