Simple MySQL syntax help

ASP Free Forums Sponsor:

July 17th, 2009, 12:17 PM

adam678

Contributing User

Join Date: Mar 2007

Posts: 141

Time spent in forums: 2 Days 15 h 22 m
Reputation Power: 15

Simple MySQL syntax help

Hi,

Is it possible to have a sort of optional WHERE clause?

For example if I have a database of shoes and I search for all size 11 shoes in white (SELECT * FROM shoes WHERE size = 11 AND colour = 'White'), but no size 11 white shoes are available, the SQL would return nothing. But Is there a way to include something in the SQL that sort of says to the database "if there are any white shoes in a size 11, display them all, otherwise just show every other shoe in size 11 nevermind what colour".

I apologise for the shoe analogy, it's the only example I could think of. But hopefully you get the idea.

Can anyone help me out?

July 17th, 2009, 08:55 PM

Lauramc

SQL Slarentice

ASP Free Intermediate (1500 - 1999 posts)

Join Date: Nov 2004

Location: In My Happy Place

Posts: 1,840

Time spent in forums: 2 Weeks 2 Days 5 h 3 m 31 sec
Reputation Power: 1266

Well the closest I can think of is CASE... but I don't know if that is available in MySQL or not. Unfortunately, I use MS-SQL, so all I can tell you is what is available there. Perhaps it will get you started at least. Here is an example:

SQL Code:

Original - SQL Code
SELECT [all_white_shoes] = SUM(CASE colour WHEN 'White' THEN 1 ELSE 0 END), [all_size_eleven] = SUM(CASE size WHEN 11 THEN 1 ELSE 0 END) FROM shoes

SELECT
    [all_white_shoes] = SUM(CASE colour WHEN 'White' THEN 1 ELSE 0 END),
    [all_size_eleven] = SUM(CASE size WHEN 11 THEN 1 ELSE 0 END)
FROM
    shoes

The other option for you is an OR. So you might be able to say:

SQL Code:

Original - SQL Code
SELECT * FROM shoes WHERE colour = 'White' OR size = 11

SELECT * FROM shoes WHERE colour = 'White' OR size = 11

Which gives you all white shoes and all size 11 shoes (regardless of color).

I know this probably isn't *exactly* what you need, but I hope it helps.

__________________
Slarentice (origin:Shadow Wizard of ASP Free) [noun] A slave and apprentice of the Wizard's Circle (specifically of mehere) at ASP Free.
----
If shemzilla takes over, it's best to be on his good side

October 13th, 2009, 01:02 AM

sakthi.tnj

Registered User

Join Date: Sep 2009

Posts: 23

Time spent in forums: 7 h 48 m 35 sec
Reputation Power: 0

Re:Simple MySQL syntax help

Hi,

I hope that the below SQL query will be useful for you.....

Code:


if exists(select * from test2 where size= '11' and color = 'white')
begin
select * from test2 where size= '11' and color = 'white'
end
else
begin
select * from test2 where size= '11'
end

Thanks & Regards
Sakthimeenakshi.S

Viewing: ASP Free Forums > Database > SQL Development > Simple MySQL syntax help

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!