MySQL Query Question - If Else or multiple select? - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

MySQL Query Question - If Else or multiple select?

Yuichim

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

10+ 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!

mylhat

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

10+ Year Member

Hello,

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

LifeinAsia

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

WebmasterWorld Administrator

10+ 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]

Yuichim

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

10+ Year Member

LifeinAsia,

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

LifeinAsia

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

WebmasterWorld Administrator

10+ Year Member

Top Contributors Of The Month

S'alright!

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

Yuichim

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

10+ Year Member

Thank you very much! I hope I can learn a lot and contribute to others!

T0ast

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

10+ 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.

LifeinAsia

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

WebmasterWorld Administrator

10+ 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.

T0ast

7:45 pm on Mar 3, 2009 (gmt 0)

10+ 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!