homepage Welcome to WebmasterWorld Guest from 54.205.122.62
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL Query Question - If Else or multiple select?
Yuichim

5+ Year Member



 
Msg#: 3858625 posted 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

5+ Year Member



 
Msg#: 3858625 posted 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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3858625 posted 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

5+ Year Member



 
Msg#: 3858625 posted 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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3858625 posted 12:28 am on Feb 27, 2009 (gmt 0)

S'alright!

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

Yuichim

5+ Year Member



 
Msg#: 3858625 posted 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

5+ Year Member



 
Msg#: 3858625 posted 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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3858625 posted 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

5+ Year Member



 
Msg#: 3858625 posted 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