-
I have a similar issue. Query 1 returns 417 records from 3 tables. Query 2 returns 399 records from 5 tables. That means 18 records are missing a Contact Person. I know I have to use a Union, and the number of columns has to be the same. But, when I add the additional columns to Query 1, and add the 2 additional tables, I always end up with the 399 records and never get the extra 18. There are a couple of linking tables, Any help?
--Query 1
SELECT A.ID "Site ID",
C.ALT_ID_NO "Facility ID",
B.INTEREST_TYPE "Interest Type",
A.NAME "Facility Name",
A.ADDR_1 "Facility Address",
A.CITY_NAME "Facility City",
A.STATE_CODE "Facility State",
A.POSTAL_CODE "Facility Zip Code",
A.COUNTY_NAME "County Name"
FROM E_SITES A
LEFT JOIN INTERESTS B ON A.ID = B.SITE_ID
LEFT JOIN ALT_IDS C ON B.RID = C.INTEREST_RID
WHERE A.ID = C.SITE_ID
AND A.APP_RID = C.APP_RID
AND C.RID = B.INTEREST_RID
AND A.APP_RID = -1
AND (A.COUNTY_NAME IN ('Allamakee', 'Benton', 'Black Hawk', 'Bremer', 'Buchanan', 'Chickasaw', 'Clayton'))
--insert
UNION ALL
--here
--Query 2
SELECT A.ID "Site ID",
C.ALT_ID_NO "Facility ID",
B.INTEREST_TYPE "Interest Type",
A.NAME "Facility Name",
A.ADDR_1 "Facility Address",
A.CITY_NAME "Facility City",
A.STATE_CODE "Facility State",
A.POSTAL_CODE "Facility Zip Code",
A.COUNTY_NAME "County Name",
E.FIRST_NAME "First Name",
E.LAST_NAME "Last Name",
E.MIDDLE_INITIAL "MI",
D.PHONE_NO "Contact Ph",
D.PHONE_EXT_NO "Ph Exten"
FROM E_SITES A
LEFT JOIN E_INTERESTS B ON A.ID = B.SITE_ID
LEFT JOIN E_ALT_IDS C ON B.RID = C.INTEREST_RID
LEFT JOIN E_AFFILIATIONS D ON A.ID = D.SITE_ID
LEFT JOIN E_INDIVIDUALS E ON D.INDIVIDUAL_RID = E.RID
WHERE A.APP_RID = B.APP_RID
AND A.APP_RID = D.APP_RID
AND A.APP_RID = -1
AND D.AFFILIATION_TYPE = ('FACILITY CONTACT'))
AND A.COUNTY_NAME IN ('Allamakee', 'Benton', 'Black Hawk', 'Bremer', 'Buchanan', 'Chickasaw', 'Clayton')
ORDER BY C.ALT_ID_NO;
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|