I have a parent table A, with 2313 rows. It has a child table B, with a one to many relationship.
I'm creating a view to display information about table A. It is critical this view does not repeat any rows from table A. However, I also need it to pull some info from its child table B.
First I tried using a LEFT JOIN to table B, but increased the rows returned (~5k). Currently I am trying to use a subquery with MAX and GROUP BY to only return information from 1 child row, but it is not working successfully, and returning ~3k rows.
I do not care which of the child rows is used to display information. I just need to ensure I only have 1 (to avoid repeating the parent in the VIEW).
SELECT
-- Rows from Table A, and enum tables (works fine).
t1.device_name_AF AS 'DEVICE NAME',
e1.issuance_name AS 'ISSUANCE',
t1.revision_number AS 'REVISION',
t1.description_combo_AF AS 'INSTRUMENT DESCRIPTION',
t2.PCDT_AF AS 'PCDT',
t2.device_description AS 'PCDT DESCRIPTION',
c2.company_name AS 'VENDOR',
t2.all_parts_used_AF AS 'PARTS',
e2.floor_key_AF AS 'FLOOR',
-- t1.zone_location AS 'ZONE',
e3.system_acronym AS 'SYSTEM',
t1.equipment_type AS 'EQUIPMENT TYPE',
t1.system_process_number AS 'SYSTEM PROCESS NUMBER',
e4.device_acronym AS 'INSTRUMENT TYPE',
t1.parallel_equipment_designator AS 'PARALLEL EQUIPMENT DESIGNATOR',
t1.pnid_drawing AS 'P&ID',
t1.location_drawing AS 'LOCATION DRAWING',
e5.model_status AS 'BIM STATUS',
t1.submittal_number AS 'SUBMITTAL NUMBER',
t1.submittal_status AS 'SUBMITTAL STATUS',
e6.company_name AS 'PROCURED BY',
e7.company_name AS 'INSTALLED BY',
e8.company_name AS 'WIRED BY',
t1.instance_comment AS 'DEVICE COMMENT',
e9.mounting_description AS 'MOUNTING STYLE',
t1.IO_point_count_AF AS 'ASSOCIATED IO COUNT',
-- Rows from Child Table B
sub1.lookup_rack_num_AF AS 'RACK',
sub1.lookup_slot_num_AF AS 'SLOT',
sub1.channel AS 'POINT',
t1.location_N_S AS 'LOCATION N/S',
t1.location_E_W AS 'LOCATION E/W',
t1.location_room AS 'LOCATION ROOM'
FROM device.instance AS t1
LEFT JOIN device.device_type_catalog AS t2 ON t2.ID_auto = t1.PCDT_ID
LEFT JOIN controls.IO_table AS t3 ON t3.parent_device_ID = t1.ID_auto
LEFT JOIN enum.issuance AS e1 ON e1.ID_auto = t1.issuance_ID
LEFT JOIN enum.building_floor AS e2 ON e2.ID_auto = t1.floor_ID
LEFT JOIN enum.process_system AS e3 ON e3.ID_auto = t1.system_ID
LEFT JOIN enum.device_name AS e4 ON e4.ID_auto = t1.instrument_type_ID
LEFT JOIN enum.BIM_status AS e5 ON e5.ID_auto = t1.BIM_status_ID
LEFT JOIN enum.company AS e6 ON e6.ID_auto = t1.procured_by_ID
LEFT JOIN enum.company AS e7 ON e7.ID_auto = t1.installed_by_ID
LEFT JOIN enum.company AS e8 ON e8.ID_auto = t1.wired_by_ID
LEFT JOIN enum.mounting_style AS e9 ON e7.ID_auto = t1.mounting_style_ID
LEFT JOIN enum.company AS c2 ON c2.ID_auto = t2.vendor_ID
LEFT JOIN
(
SELECT
MAX(x3.lookup_rack_num_AF) AS lookup_rack_num_AF,
MAX(x3.lookup_slot_num_AF) AS lookup_slot_num_AF,
MAX(x3.channel) AS channel,
x3.parent_device_ID
FROM controls.IO_table AS x3
GROUP BY x3.parent_device_ID
)
AS sub1 ON sub1.parent_device_ID = t1.ID_auto
WHERE (t1.PCDT_ID IS NOT NULL) AND (t2.is_instrument = 1)
You are thinking correctly here - and your ( ) sub1 subquery appears that it will logically only produce 1 row. Therefore that join should not increase the rows at all, and so I would try removing that section to see if the 700 extra rows might be coming from one of the other joins.