homepage Welcome to WebmasterWorld Guest from 54.198.130.203
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL Query Question - If Else or multiple select?
Yuichim




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

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




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

Hello,

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

LifeinAsia




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

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




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

LifeinAsia,

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

LifeinAsia




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

S'alright!

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

Yuichim




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

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

T0ast




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

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




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

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




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

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!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved