Count for 2 tables

Transact-SQL
1

I have 3 tables to join

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

NodesData looks like this

Interfaces

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

Volumes

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

2

Something like this perhaps:

select a.nodeid
      ,a.caption
      ,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
         ,a.caption
;
3

Thanks bitsmed, I tried that but having me this error

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

4

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.