-1

I have 3 tables: SWPPP_SITE, SWPPP_TRACKING, and LKUP_CONSTRUCTION_STATUS.

The SWPPP_SITE is 1:many with SWPPP_TRACKING. The SWPPP_SITE is 1:1 with LKUP_CONSTRUCTION_STATUS.

I'm trying to select 1 record for a unique site for all sites. This query gives me multiple records for each site -- 1 site, many tracking, and the corresponding status.

How can I retrieve only e.g. one record for Test_1 and one for Test_site3, per the image?

SELECT s.OBJECTID As SITE_OID, s.Site_Name AS SITE_Site_Name, 
    s.Construction_StatusID, t.OBJECTID As TRACKING_OID, t.Site_Name As 
    TRACKING_Site_Name, cs.OBJECTID As CON_STAT_OID, cs.Status
FROM SWPPP_SITE s INNER JOIN SWPPP_TRACKING t ON s.Site_Name = t.Site_Name
INNER JOIN LKUP_CONSTRUCTION_STATUS cs ON s.Construction_StatusID = 
    cs.OBJECTID
WHERE t.Completion_Date Between '1/1/18' And '5/1/18';

enter image description here

Kevin R. M.
  • 25
  • 1
  • 11
  • 1
    If you only want one row per site, which row from tracking do you want? – Tab Alleman Apr 06 '18 at 18:02
  • Remove those unnecessary brackets. Why do you bracket everything?? It makes it hard to read. – Eric Apr 06 '18 at 18:19
  • Also learn to use table alias. The long table names is very annoying to read. – Eric Apr 06 '18 at 18:20
  • Tab is spot on. You're getting multiple records because there are multiple tracking records for each site; for example, site 21 has two tracking records 46 and 47. – Adam Apr 06 '18 at 19:13
  • OK. The site info is the same, say for site 21 across tracking records 46 and 47. Note: a superset of these data are going into a report, hence the need to eliminate duplicate sites. – Kevin R. M. Apr 06 '18 at 21:19

1 Answers1

1

you can just SELECT DISTINCT..., and only include the columns that you care about (i.e., remove TRACKING_OID)

ben
  • 199
  • 4