[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Help on a Select Query

Posted on 2007-11-22
8
Medium Priority
?
278 Views
Last Modified: 2013-12-07
I have the following select Query

select a.Date,c.categoryid,count(*),k.countFemale,m.countMale,sum(a.weight), (sum(a.weight)/16*100)
from animals_arrived a,categories c,items i,(select ai.Date,ci.categoryid,ii.itemid,count(*) as countFemale
from animals_arrived ai,categories ci,items ii
where ai.supplierid = 1 and ci.categoryid= 1 and ci.categoryid = ii.categoryid
and ii.itemid = ai.itemid and ai.itemid = 1
group by ai.Date,ci.categoryid,ii.itemid) k,
(select ai.Date,ci.categoryid,ii.itemid,count(*) as countMale
from animals_arrived ai,categories ci,items ii
where ai.supplierid = 1 and ci.categoryid= 1 and ci.categoryid = ii.categoryid
and ii.itemid = ai.itemid and ai.itemid = 2
group by ai.Date,ci.categoryid,ii.itemid) m
where a.supplierid = 1 and c.categoryid= 1 and c.categoryid = i.categoryid and i.itemid = a.itemid
and c.categoryid=k.categoryid and i.itemid = k.itemid and a.Date = k.date
and c.categoryid=m.categoryid and i.itemid = m.itemid and a.Date = m.date
group by a.Date,c.categoryid,k.countFemale,m.countMale


Now if the "m" table or the "k" table returns nothing then nothing comes out from the query. I don't want that. What i want if no row that matches with the criteria comes out then set countFemale or countMale as ZERO.
Can you please help me ?

 
0
Comment
Question by:cscg1976
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 20337621
you will have to LEFT JOIN the "m" and "k" tables.

left me try:

select a.Date,c.categoryid,count(*),k.countFemale,m.countMale,sum(a.weight), (sum(a.weight)/16*100)
from animals_arrived a
inner join items i
 on i.itemid = a.itemid
inner join categories c
 on c.categoryid = i.categoryid 
and c.categoryid= 1 
left join (select ai.Date,ci.categoryid,ii.itemid,count(*) as countFemale
from animals_arrived ai,categories ci,items ii
where ai.supplierid = 1 and ci.categoryid= 1 and ci.categoryid = ii.categoryid 
and ii.itemid = ai.itemid and ai.itemid = 1 
group by ai.Date,ci.categoryid,ii.itemid) k
  on i.itemid = k.itemid 
 and a.Date = k.date 
 and c.categoryid=k.categoryid 
left join (select ai.Date,ci.categoryid,ii.itemid,count(*) as countMale
from animals_arrived ai,categories ci,items ii
where ai.supplierid = 1 and ci.categoryid= 1 and ci.categoryid = ii.categoryid 
and ii.itemid = ai.itemid and ai.itemid = 2 
group by ai.Date,ci.categoryid,ii.itemid) m
  on c.categoryid=m.categoryid 
 and i.itemid = m.itemid 
 and a.Date = m.date 
where a.supplierid = 1 
group by a.Date,c.categoryid,k.countFemale,m.countMale

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20337675
that query looks.. very interesting. it can be greatly simplified. brb
0
 
LVL 25

Accepted Solution

by:
imitchie earned 800 total points
ID: 20337704
run this query against the same database, and see if you get the same result as angel's
SELECT   a.DATE,
         c.categoryid,
         COUNT(* ),
         CASE i.item_id WHEN 1 THEN 1 ELSE 0 END AS countfemale,
         CASE i.item_id WHEN 2 THEN 1 ELSE 0 END AS countmale,
         SUM(a.weight),
         (SUM(a.weight) / 16 * 100)
FROM     animals_arrived a
INNER JOIN items i on i.itemid = a.itemid
INNER JOIN categories c on c.categoryid = i.categoryid and c.categoryid = 1
WHERE    a.supplierid = 1
GROUP BY a.DATE,c.categoryid

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 6

Assisted Solution

by:Rajesh_mj
Rajesh_mj earned 800 total points
ID: 20338337

Based on imitchie code,

SELECT   a.DATE,
         c.categoryid,
         COUNT(* ),
         Sum(CASE i.item_id WHEN 1 THEN 1 ELSE 0 END) AS countfemale,
         Sum(CASE i.item_id WHEN 2 THEN 1 ELSE 0 END) AS countmale,
         SUM(a.weight),
         (SUM(a.weight) / 16 * 100)
FROM     animals_arrived a
INNER JOIN items i on i.itemid = a.itemid
INNER JOIN categories c on c.categoryid = i.categoryid and c.categoryid = 1
WHERE    a.supplierid = 1
GROUP BY a.DATE,c.categoryid
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20340621
yup mymistake forgot sum.. good one rajesh
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20521018
Wow, I completely recoded your query, and .... hmm... nice one
0
 

Author Comment

by:cscg1976
ID: 20521122
My mistake. I wanted to give you half points. Can it be undone ???
I apologize again.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question