January 18, 2006 at 9:08 am
I need to report on the number of unique locations that meet certain criteria and to include translations for some of the codes on the location table. Now a location can have more than one address (sounds weird I know) but I only need to list one address for each location (which one doesn't matter). Also, a location can have more than one telecomm but again I only need to include one.
Below is some SQL that almost works. However, while the WHERE clause identifies 255 locations to be reported the statement as a whole returns 293 rows. The problem is if I use a GROUP BY on location_id I need to put the other returned columns into aggregate functions.
How do I ensure that what is in address_1 for any given row relates to what is in address_2 for that row. Ditto for the other related columns.
Regards PLST
-----
SELECT l.location_id, l.location_name, a.address_1, a.address_2, a.address_3, a.address_4, a.town, a.county, a.post_code, t.comm_info, b.brick_description
FROM tbl_locations l
LEFT JOIN tbl_location_bricks lb ON lb.location_id = l.location_id
LEFT JOIN tbl_bricks b ON b.brick_id = lb.brick_id
LEFT JOIN tbl_location_addresses la ON la.location_id = l.location_id
LEFT JOIN tbl_addresses a ON a.address_id = la.address_id
LEFT JOIN tbl_location_tele_comm lt ON lt.location_id = l.location_id
LEFT JOIN tbl_tele_comm t ON t.tele_comm_id = lt.tele_comm_id
WHERE l.location_id IN (
SELECT l2.location_id
FROM tbl_locations l2
INNER JOIN tbl_location_contacts lc ON lc.location_id = l2.location_id
INNER JOIN tbl_contacts c ON c.contact_id = lc.contact_id
INNER JOIN tbl_projects p on p.project_id = c.project_id
WHERE l2.active = 1
AND p.project_name = 'Pfizer - CHD'
AND l2.created < '2006-01-01'
GROUP BY l2.location_id
UNION
SELECT l3.location_id
FROM tbl_locations l3
INNER JOIN tbl_location_customers lc ON lc.location_id = l3.location_id
INNER JOIN tbl_customer_contacts cc ON cc.customer_id = lc.customer_id
INNER JOIN tbl_contacts c ON c.contact_id = cc.contact_id
INNER JOIN tbl_projects p on p.project_id = c.project_id
WHERE l3.active = 1
AND p.project_name = 'Pfizer - CHD'
AND l3.created < '2006-01-01'
GROUP BY l3.location_id)
January 18, 2006 at 10:20 am
I figured it out. Using temporary tables and (because the keys are GUIDs) CASTing into binary:
Regards PLST
-----
SELECT l.location_id, MAX(CAST(a.address_id AS BINARY(16))) AS address_id, MAX(CAST(t.tele_comm_id AS BINARY(16))) AS tele_comm_id, MAX(CAST(b.brick_id AS BINARY(16))) AS brick_id
INTO #mytable
FROM tbl_locations l
LEFT JOIN tbl_location_bricks lb ON lb.location_id = l.location_id
LEFT JOIN tbl_bricks b ON b.brick_id = lb.brick_id
LEFT JOIN tbl_location_addresses la ON la.location_id = l.location_id
LEFT JOIN tbl_addresses a ON a.address_id = la.address_id
LEFT JOIN tbl_location_tele_comm lt ON lt.location_id = l.location_id
LEFT JOIN tbl_tele_comm t ON t.tele_comm_id = lt.tele_comm_id
WHERE l.location_id IN (
SELECT l2.location_id
FROM tbl_locations l2
INNER JOIN tbl_location_contacts lc ON lc.location_id = l2.location_id
INNER JOIN tbl_contacts c ON c.contact_id = lc.contact_id
INNER JOIN tbl_projects p on p.project_id = c.project_id
WHERE l2.active = 1
AND p.project_name = 'Pfizer - CHD'
AND l2.created < '2006-01-01'
GROUP BY l2.location_id
UNION
SELECT l3.location_id
FROM tbl_locations l3
INNER JOIN tbl_location_customers lc ON lc.location_id = l3.location_id
INNER JOIN tbl_customer_contacts cc ON cc.customer_id = lc.customer_id
INNER JOIN tbl_contacts c ON c.contact_id = cc.contact_id
INNER JOIN tbl_projects p on p.project_id = c.project_id
WHERE l3.active = 1
AND p.project_name = 'Pfizer - CHD'
AND l3.created < '2006-01-01'
GROUP BY l3.location_id)
GROUP BY l.location_id
SELECT tt.location_id, l.location_name, a.address_1, a.address_2, a.address_3, a.address_4, a.town, a.county, a.post_code, t.comm_info, b.brick_description
FROM #mytable tt
LEFT JOIN tbl_locations l ON l.location_id = tt.location_id
LEFT JOIN tbl_bricks b ON b.brick_id = tt.brick_id
LEFT JOIN tbl_addresses a ON a.address_id = tt.address_id
LEFT JOIN tbl_tele_comm t ON t.tele_comm_id = tt.tele_comm_id
DROP TABLE #mytable
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply