CODEWITHSUNDEEP
sql-serverjoin
lulala
lulala

Reputation: 647

inner join using same table

I have two sql queries (from 1 table) that I need to join but I'm always getting an error when I tried to join them.

1st table: This is the complete table. 

Select Server, Network, ReservationIP, ReservationMAC, ReservationName
from ScopeReservations

Result (truncated. this is a very table)

> Server    Network ReservationIP   ReservationMAC  ReservationName
> Server1   10.3.64.32  10.3.64.36  000d5d08e83c    pr-bnea06-a01-a
> Server2   10.3.64.32  10.3.64.37  000d5d08278c    pr-bnea06-a02-a

2nd table: All those with duplicate value in ReservationMAC

 Select ReservationMAC, COUNT(*) from DHCP_ScopeReservations
    group by ReservationMAC
    having count(*) > 1
    order by ReservationMAC

Result: 

ReservationMAC  (No column name)
0000850a3477    2
0000854b8328    2
85731867    2

The result that I need is this. 

Server  Network         ReservationIP   ReservationMAC  ReservationName
Server1 10.233.192.0    10.233.192.5    0000850a3477    pq2217a.
Server2 10.233.196.0    10.233.197.129  0000850a3477    pq2217.
Server1 10.80.16.0      10.80.17.13     0000854b8328    PQ090.
Server2 10.80.70.32     10.80.70.47     0000854b8328    Canon4B8328.

Also, Result should not contain those with the same reservationIP and reservationMAC. Not included in result:

Server  Network     ReservationIP  ReservationMAC   ReservationName
Server1 10.34.57.0  10.34.57.10    011               vh101r
Server2 10.34.57.1  10.34.57.10    011               vh102r
Server1 10.206.0.0  10.206.3.22    0000681569af      ac-gpo069
Server2 10.206.0.0  10.206.3.22    0000681569af      ac-gpo069

The result should all those with duplicate entry in ReservationMac column. The result must contain all columns of the table.

What I tried to do: 

Select a.Server, a.Network, a.ReservationIP, a.ReservationMAC,
a.ReservationName 
from DHCP_ScopeReservations a
INNER JOIN 
    (
        Select ReservationMAC, COUNT(*)
        from DHCP_ScopeReservations
        group by ReservationMAC
        having count(*) > 1
    ) dt ON a.ReservationMAC=dt.ReservationMAC

Error was

Msg 8155, Level 16, State 2, Line 6 No column name was specified for column 2 of 'dt'.

Upvotes: 0

Views: 56

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

You need to declare an alias for COUNT(*):

Select a.Server, a.Network, a.ReservationIP, 
       a.ReservationMAC, a.ReservationName, dt.cnt 
from DHCP_ScopeReservations a   
INNER JOIN (Select ReservationMAC, COUNT(*) AS cnt
            from DHCP_ScopeReservations             
            group by ReservationMAC               
            having count(*) > 1) dt 
ON a.ReservationMAC=dt.ReservationMAC

Using the cnt alias you can now access the number of duplicates in the SELECT clause of the outer query.

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16544

Using a JOIN you can get the desired result, like this:

SELECT Server, Network, ReservationIP, s.ReservationMAC, ReservationName
FROM ScopeReservations s
JOIN (
  SELECT ReservationMAC 
  FROM DHCP_ScopeReservations
  GROUP BY ReservationMAC
  HAVING COUNT(ReservationMAC) > 1
) dupe
ON s.ReservationMAC = dupe.ReservationMAC

Upvotes: 1

Related Questions