How to write a join SQL 1

[tofuTnT]

hi all,

I am looking for way to write a query that would give me what I want.
Basically, there are four tables:

Event
EventID
EventName
EventDate

EventToSubType
EventID
SubTypeID

SubType
SubTypeID
SubTypeName
MasterTypeID

MasterType
MasterTypeID
MasterTypeName

I hope the tables definition are self-explainatory.
Here is what I like to have
MasterTypeName, Count(*)
MType1, 5
MType2, 3
MType3, 0
MType4, 10

based on some EventDate range. Because MType3 could be 0, so I tried various way with outer join and never was able to get MType3 to show up.

Thanks in advance.

tofuTnT

http://www.ezonetechnology.com

New York Desktop Support IT Network Setup Web Design Data Processing Windows 2000 Server

 

[SantaMufasa]

Tofu,

Here is some code that works for you:

select mt.mastertypename, count(*)
from Event e, EventToSubType etst, SubType st, MasterType mt
where e.eventid  = etst.eventid
  and etst.subtypeid  = st.subtypeid
  and st.mastertypeid  = mt.mastertypeid
group by mt.mastertypename
union
(select mastertypename,0
   from mastertype
minus
select mt.mastertypename, 0
  from Event e, EventToSubType etst, SubType st, MasterType mt
 where e.eventid  = etst.eventid
   and etst.subtypeid  = st.subtypeid
   and st.mastertypeid  = mt.mastertypeid);

MASTERTYPE   COUNT(*)
---------- ----------
MType1              5
MType2              3
MType3              0
MType4             10

4 rows selected.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tofuTnT]

Mufasa,

Thanks a lot.. I never even thought of using UNION and MINUS
Guess I need to practice write SQL a little more..


tofuTnT

http://www.ezonetechnology.com

New York Desktop Support IT Network Setup Web Design Data Processing Windows 2000 Server