Show all names after WHERE

Dev Shed Forums Sponsor:

June 10th, 2009, 09:50 AM

ashecorven

Registered User

Join Date: Jun 2009

Posts: 9

Time spent in forums: 1 h 37 m 14 sec
Reputation Power: 0

Show all names after WHERE

Hello,

Here is one of my current queries:

SELECT name, sum(duration)
FROM master
WHERE class like '%tivoli 1%' and class not like '%tivoli 2%'
group by name;

This will give a result of:
Name1 6.5
Name3 50
Name6 64

but i would like the result to be:
Name1 6.5
Name2 0
Name3 50
Name4 0
Name5 0
Name6 64
Name7 0
etc.

This seems so simple but i cannot figure out how to do it, i've tried isnull, coalesce, having sum(duration)>-1.

Any help appreciated.

I've had r937's help in the MySQL forums to get it working in MySQL.

BUT i also need it to work in MS Access 2002! If anyone's got any ideas, it's more than appreciated. Thanks.

June 11th, 2009, 10:24 AM

MadDogBrown

Contributing User

Join Date: Mar 2009

Posts: 730

Time spent in forums: 1 Week 1 Day 3 h 40 m 5 sec
Reputation Power: 516

Quote:

Here is one of my current queries:

Code:

SELECT name, sum(duration)
FROM master
WHERE class like '%tivoli 1%' and class not like '%tivoli 2%'
group by name;

This will give a result of:
Name1 6.5
Name3 50
Name6 64

but i would like the result to be:
Name1 6.5
Name2 0
Name3 50
Name4 0
Name5 0
Name6 64
Name7 0
etc.

June 11th, 2009, 11:43 AM

BenWill

Registered User

Join Date: Jun 2009

Posts: 16

Time spent in forums: 1 Day 2 h 51 m 46 sec
Reputation Power: 0

I *think* I know what you mean.. I created what you wanted but... needed to use a lookup (Listing all of the 'names.. name1-name7' and then left joining against it...)

Anyway if its no use sorry, otherwise :

SELECT ListOfAllNames.ListNames, Sum(Master.value) AS SumOfvalue
FROM ListOfAllNames LEFT JOIN Master ON ListOfAllNames.ListNames = Master.name
WHERE (((Master.class) Is Null Or ((Master.class) Like "*tivoli 1*" And (Master.class) Not Like "*tivoli 2*")))
GROUP BY ListOfAllNames.ListNames;

June 14th, 2009, 05:06 AM

ashecorven

Registered User

Join Date: Jun 2009

Posts: 9

Time spent in forums: 1 h 37 m 14 sec
Reputation Power: 0

Quote:

Originally Posted by BenWill

Hi thanks for the reply.

The query you listed is what i have working in MySQL (thanks to R937 in the MySQL forums) but MS Access 2002 does not like it, do you have any ideas why?/how to work around it?

And yes, what you did get the right idea of what i meant.

June 14th, 2009, 09:15 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,687

Time spent in forums: 2 Months 1 Week 3 Days 9 h 27 m 54 sec
Reputation Power: 2458

try this --

Code:

SELECT names.name
     , SUM(tivoli.duration) AS master_duration
  FROM ( SELECT DISTINCT name
           FROM master ) AS names
LEFT OUTER
  JOIN ( SELECT name
              , duration
           FROM master
          WHERE class LIKE '%tivoli 1%' 
            AND class NOT LIKE '%tivoli 2%' ) as tivoli
    ON tivoli.name = names.name
GROUP 
    BY names.name;

i think you might have to change the percents to asterisks for the LIKE strings

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

June 14th, 2009, 10:44 AM

ashecorven

Registered User

Join Date: Jun 2009

Posts: 9

Time spent in forums: 1 h 37 m 14 sec
Reputation Power: 0

Quote:

Originally Posted by r937

try this --

Code:

SELECT names.name
     , SUM(tivoli.duration) AS master_duration
  FROM ( SELECT DISTINCT name
           FROM master ) AS names
LEFT OUTER
  JOIN ( SELECT name
              , duration
           FROM master
          WHERE class LIKE '%tivoli 1%' 
            AND class NOT LIKE '%tivoli 2%' ) as tivoli
    ON tivoli.name = names.name
GROUP 
    BY names.name;

i think you might have to change the percents to asterisks for the LIKE strings

Yeah already tried both the % and *'s, but still not able to get it working.

June 14th, 2009, 02:55 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,687

Time spent in forums: 2 Months 1 Week 3 Days 9 h 27 m 54 sec
Reputation Power: 2458

Quote:

Originally Posted by ashecorven

... not able to get it working.

oh, i can see the error message from here -- it's obvious what the problem is!!!

June 15th, 2009, 04:39 AM

ashecorven

Registered User

Join Date: Jun 2009

Posts: 9

Time spent in forums: 1 h 37 m 14 sec
Reputation Power: 0

Quote:

Originally Posted by r937

it's obvious what the problem is!!!

MS ACCESS?

The suspense is killing me!

June 19th, 2009, 02:14 AM

ashecorven

Registered User

Join Date: Jun 2009

Posts: 9

Time spent in forums: 1 h 37 m 14 sec
Reputation Power: 0

Anyone?

#10

June 19th, 2009, 06:21 AM

BenWill

Registered User

Join Date: Jun 2009

Posts: 16

Time spent in forums: 1 Day 2 h 51 m 46 sec
Reputation Power: 0

Not too sure sorry, works fine in access 2003 for me! (The one I wrote).. all can think is to check all the column data types, tried it without wildcards to get a direct match first? (And eliminate them being the problem)

Viewing: Dev Shed Forums > Databases > Database Management > Show all names after WHERE

« 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!