?
Solved

Help on a Select Query

Posted on 2007-11-22
8
Medium Priority
?
274 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

764 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