I have 2 tables that I need to join and select the unique rows from. Here is a sample of my data: (there are more columns)
tbl1:
MB# MBName PCCNo_PRI Primary_IP PCCNo_SEC Secondary_IP ID
100 name 0 10.1.9.10 30 10.1.9.10 1
103 name3 17 10.1.9.27 47 10.1.9.67 4
403 name13 17 10.1.9.27 47 10.1.9.67 14
tbl2:
RTU PCC#_PRI PCC#_SEC STATION ADDRESS
15 0 30 6
52 12 42 1
53* 17 47 1
54 18 48 1
63 9 39 2
69* 17 47 2
I need to join the two tables and get the unique RTU(s) in tbl2 for a given MB# in tbl1. Query =
SELECT t1.MB#,t2.RTU,t2.[Device Manufacturer],t2.PCC#_PRI,t2.PCC#_SEC,t2.[STATION ADDRESS]
INTO C300_RTU_MASTERBLK_Map
FROM mbm_PCDIMasterBlk_tbl as t1, dbo.WOA_PCC_Conn_tbl as t2
WHERE t1.PCCNo_PRI = t2.PCC#_PRI
I am getting duplicate rows for tbl2 53 and 69 (* above). 53 ends up with 2 entries; one to 103 and one 403 (69 gets same). How can I query this for unique RTU(s) to MB#?
The duplicate rows appears because you join on "17" which gives 2 rows on each side
Then, as it stands, you can't with that SELECT list.
How do you decide which t1.MB#
you want for the t2 columns?
There is no secondary JOIN column that I can see.
So the best you can get is use MAX (or MIN) to pick either 403 or 103.
SELECT
MAX(t1.MB#) AS MB#,
t2.RTU,t2.[Device Manufacturer],t2.PCC#_PRI,t2.PCC#_SEC,t2.[STATION ADDRESS]
INTO C300_RTU_MASTERBLK_Map
FROM
dbombm_PCDIMasterBlk_tbl as t1
JOIN
dbo.WOA_PCC_Conn_tbl as t2 ON t1.PCCNo_PRI = t2.PCC#_PRI
GROUP BY
t2.RTU,t2.[Device Manufacturer],t2.PCC#_PRI,t2.PCC#_SEC,t2.[STATION ADDRESS]