Welcome to WebmasterWorld Guest from 23.22.46.195

Forum Moderators: open

MySQL Query Question - If Else or multiple select?

   
8:54 pm on Feb 26, 2009 (gmt 0)

5+ Year Member



Hi, I have a table "log" that displays the following.

1. Date
2. Number
3. Status

And the table looks like this.

--------------------------------------
Date Number Status
--------------------------------------
1/23/09 960-000-9542 4500
--------------------------------------
1/23/09 515-555-1212 3200
--------------------------------------
1/23/09 515-555-1212 0
--------------------------------------
1/23/09 212-000-2315 0
--------------------------------------
1/23/09 653-000-1111 6532
--------------------------------------

Is there a way to do a single query to show the
results like this?
(Show all records that don't have the status of "0". If the record has status of "0" somewhere in the other row, then do not show)

--------------------------------------
Date Number Status
--------------------------------------
1/23/09 960-000-9542 4500
--------------------------------------
1/23/09 653-000-1111 6532
--------------------------------------

I've been trying to do this for a while but had no luck. Any help appreciated! Thank!

11:09 pm on Feb 26, 2009 (gmt 0)

5+ Year Member



Hello,

You should be able to do something like this:
select * from log where status !="0";

11:28 pm on Feb 26, 2009 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



This should give you what you want:
SELECT *
FROM log
WHERE Number NOT IN (SELECT Number FROM log WHERE Status=0)

[edited by: LifeinAsia at 11:28 pm (utc) on Feb. 26, 2009]

12:18 am on Feb 27, 2009 (gmt 0)

5+ Year Member



LifeinAsia,

Thank you very much! It worked great!
I just learned something new today. Thank you!

12:28 am on Feb 27, 2009 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



S'alright!

Oh, and welcome to WebmasterWorld [webmasterworld.com]!

12:33 am on Feb 27, 2009 (gmt 0)

5+ Year Member



Thank you very much! I hope I can learn a lot and contribute to others!
6:20 pm on Mar 3, 2009 (gmt 0)

5+ Year Member



I don't think != works, but <> seems to. So:
SELECT * FROM log WHERE status <> 5
works for me.

Is there an advantage to using LifeinAsia's method over this? The logic seems the same in both of them.

6:53 pm on Mar 3, 2009 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



Completely different logic. Remember from the OP:
If the record has status of "0" somewhere in the other row, then do not show

With SELECT * FROM log WHERE status <> 0, you'd still select the 2nd row in the sample.
7:45 pm on Mar 3, 2009 (gmt 0)

5+ Year Member



I see, I didn't read the question carefully. If a phone number ever has a status of 0, then don't select it. Thanks!
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month