Joining Problem..................

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining Problem..................

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2008-11-01 : 08:36:26

im using a ms sql server 2000

transmas
---------------
transno --pk
vid --fk
transdate

transdet
---------------
transid --fk
itemid --fk

itemmas
-----------
itemid --pk
idesc

itemdet
------------
itemid --fk
cid --fk

classification
--------------------
cid ----pk
cdesc

iam having problem with my join...........please help me
i need to count the classification in every transaction
some of the classification is null
when iam trying to join it iam having problem
it only view the data that is not null

i've tried full outer join but is return the row with the same value
please help......................

Select transmas.transno,transmas.trandate,transmas.vid,class1.dangerous,class2.[Non Prime],class3.[Prime],
class4.[Vehicle] from transmas
LEFT join((Select transmas.transno,count(classification.cid) as Dangerous from vessel inner join(
transmas inner join(transdet inner join(itemmas inner join
(itemdet inner join classification ON itemdet.cid=classification.cid) ON itemmas.itemid=itemdet.itemid)
ON itemmas.itemid=transdet.itemid) ON transmas.transno=transdet.transid) ON vessel.vid=transmas.vid
WHERE classification.cdesc='Dangerous'
GROUP BY transmas.transno)class1 LEFT join ((Select transmas.transno,count(classification.cid) as [Non Prime] from vessel inner join(
transmas inner join(transdet inner join(itemmas inner join
(itemdet inner join classification ON itemdet.cid=classification.cid) ON itemmas.itemid=itemdet.itemid)
ON itemmas.itemid=transdet.itemid) ON transmas.transno=transdet.transid) ON vessel.vid=transmas.vid
WHERE classification.cdesc='Non Prime'
GROUP BY transmas.transno)class2 LEFT join ((Select transmas.transno,count(classification.cid) as [Prime] from vessel inner join(
transmas inner join(transdet inner join(itemmas inner join
(itemdet inner join classification ON itemdet.cid=classification.cid) ON itemmas.itemid=itemdet.itemid)
ON itemmas.itemid=transdet.itemid) ON transmas.transno=transdet.transid) ON vessel.vid=transmas.vid
WHERE classification.cdesc='Prime'
GROUP BY transmas.transno)class3 LEFT JOIN (Select transmas.transno,count(classification.cid) as [Vehicle] from vessel inner join(
transmas inner join(transdet inner join(itemmas inner join
(itemdet inner join classification ON itemdet.cid=classification.cid) ON itemmas.itemid=itemdet.itemid)
ON itemmas.itemid=transdet.itemid) ON transmas.transno=transdet.transid) ON vessel.vid=transmas.vid
WHERE classification.cdesc='Vehicle'
GROUP BY transmas.transno)class4 on class3.transno=class4.transno)
on class2.transno=class3.transno)on class1.transno=class2.transno)on transmas.transno=class1.transno

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-01 : 12:04:43

i think you want this

SELECT t.transno,
t.vid,
t.transdate,
t1.Dangerous,
t1.[Non Prime],
t1.Prime,
t1.Vehicle
FROM transmas t
INNER JOIN transdet td
ON td.transid=t.transno
INNER JOIN
(SELECT id.itemid,
COUNT(CASE WHEN cdesc='Dangerous' THEN c.cid ELSE NULL END) AS Dangerous,
COUNT(CASE WHEN cdesc='Non Prime' THEN c.cid ELSE NULL END) AS [Non Prime],
COUNT(CASE WHEN cdesc='Prime' THEN c.cid ELSE NULL END) AS Prime,
COUNT(CASE WHEN cdesc='Vehicle' THEN c.cid ELSE NULL END) AS Vehicle
FROM itemdet id
INNER JOIN classification c
ON c.cid=id.cid
GROUP BY id.itemid
)t1
ON t1.itemid=td.itemid

wormz666
Posting Yak Master

110 Posts

Posted - 2008-11-02 : 04:51:41

this is the output i want

transno vid v.vdesc dangerous prime no prime vechile
----------------------------------------------------
1332 1 xxxx 5 6 8 null
1331 1 xxxx 3 null 8 null
1443 2 dddd null null 7 null

the data is this

tranmas
----------
tranno transdate
-----------------
1332 10-01-2008
1331 10-21-2008
1443 10-21-2008

transdet
------------------
transid itemid
------------------
..........
..........
.........

-----------------------------------------------------------------------------
i need to classify the item and count them according in there classification
------------------------------------------------------------------------------
please help.............

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 10:40:06

what does my suggestion return?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources