query help

I have the following tables:

Machines
_______________
MachineID
MachineName
MachineMACAddr

Adapters
_________
AdapterID
MachineID
AdapterNumber
MACAddr

Machines
_______________________
1      Test1      Test133
2      Test2      Test233
3      Test3      Test333


Adapters
________________________________________________________
AdapterID      MachineID      AdapterNumber      MACAddr
_________________________________________________________
1      1      0            Test245
2      1      1            Test5877
3      1      2            Test111
4      2      0            Test222
5      3      0            Test798
6      3      1            Test5709
7      3      2            Test366


Note that Machines with MachineID 1 and 3 have 3 Adapters each (AdapterNumber 0,1, and 2)
and Machine with MachineID=2 has only one adapter (with AdapterNumber = 0)

I would like to return the following data

SELECT m.MachineName, m.MachineMACAddr, a.MACAddr
FROM Machines m INNER JOIN Adapters a ON m.MachineID=a.MachineID

but only from those machines that have either just one adopter (with AdapterNumber=0) or have more than one adapter (in that case I want to return MACAddr for the record where AdapterNumber=2)

SELECT m.MachineName, m.MachineMACAddr, a.MACAddr
FROM Machines m INNER JOIN Adapters a ON m.MachineID=a.MachineID
WHERE a.AdapterNumber=2

will only return records for the machines that have more than one adapter :

_______________________________________________________________
MachineName      MachineMACAddr            MACAddr
_______________________________________________________________
Test1            Test133                  Test111
Test3            Test333                  Test366

while I want the following data returned:

_______________________________________________________________
MachineName      MachineMACAddr            MACAddr
________________________________________________________________
Test1            Test133                  Test111
Test2            Test233                  Test222
Test3            Test333                  Test366

Please help


Start Free Trial