SQLTeam.com | Weblogs | Forums

Count for 2 tables

I have 3 tables to join

1st Table: NodesData
2nd Table: Interfaces
3rd Table: Volumes

NodesData looks like this


 NodeID	 InterfaceID		InterfaceName
 1	     642	     	    GigabitEthernet0/0
 1		 643	     		Port-channel1
 2		 8	     		    GigabitEthernet0/0


NodeID	VolumeID		VolumeName
5		1				C:\ Label: 8191319
5		2				Virtual Memory
5		3				Physical Memory
7		4				C:\ Label:OS 8191319

Output would be like. Bi

NodesData.NodeID	NodesData.Caption	COUNT(Interfaces.InterfaceID)	COUNT (Volumes.VolumeID)
1					bas-2821-1-aus		5					            2

How I can achieve this?

Thanks in Advance

Something like this perhaps:

select a.nodeid
      ,count(distinct b.interfaceid) as interface_count
      ,count(distinct c.volumeid) as volume_count
  from nodesdata as a
       left outer join interfaces as b
                    on b.nodeid=a.nodeid
       left outer join volumes as c
                    on c.nodeid=a.nodeid
 group by a.nodeid

Thanks bitsmed, I tried that but having me this error

Warning: Null value is eliminated by an aggregate or other SET operation.

Either ignore the warning (a NULL value will be excluded from the COUNT), or explicitly prevent NULL values being included in the COUNT()

Warning messages can disrupt APPs that are not expecting . handling them, but maybe that won't be a problem for you.