I need to extract a single row or data from a query that uses left outer joins and has multiple rows returned.

Currently, I get the System ID, Product Version and Contract information from the query. For simplicity let's say the result is this:

503220 7.0.2CM04 Active

If I include the last required field from another table (team_BUS_ORG.NAME), the results triple because there are multiple values in that last table/field, similar to this:

'503220' '7.0.2CM04' 'Active' 'T01- Cust Support'
'503220' '7.0.2CM04' 'Active' 'Field Support-East'
'503220' '7.0.2CM04' 'Active' 'Acme widgets'

The problem is the user requirement is to show one single record and include the 'Cust Support' entry if it exists on a System, but trying to include "where team_BUS_ORG.NAME like '%Cust Support'" in the SQL statement as criteria will eliminate any record that doesn't have a 'Cust Support' match from the result set. And there are Systems that have no 'Cust Support' entry so the entire system would be eliminated from the result set. I'm trying to figure out how to get one returned record per system id, even if there is no 'Cust Support' entry, but include the 'Cust Support' field value in the result set if it exists.

I've been looking at the developer's and reference guides at the QueryNew, QueryAddRow and QuerySetCell functions along with cfloop, but I'm not sure if that is the right direction. Does anyone have a suggestion?

The query would look something like this if I were to include the business org field in the query (if seeing the query helps)...

SELECT
sys_SITE_PART.SERIAL_NO AS SYSTEMID,
sys_MOD_LEVEL.MOD_LEVEL AS RELEASE,
sys_CONTRACT.STATUS AS CONTRACTSTATUS,
REPLACE(team_BUS_ORG.NAME, ' - Product Support Team',') AS TEAM_NAME

FROM   (((((SA.TABLE_SITE_PART sys_SITE_PART
INNER JOIN SA.TABLE_MOD_LEVEL sys_MOD_LEVEL ON sys_SITE_PART.SITE_PART2PART_INFO=sys_MOD_LEVEL.OBJID)
INNER JOIN SA.MTM_SITE_PART24_CONTRACT5 sys_SITE_PART24_CONTRACT5 ON sys_SITE_PART.OBJID=sys_SITE_PART24_CONTRACT5.DIR_SITEPART2CONTRACT)
INNER JOIN SA.TABLE_CONTRACT sys_CONTRACT ON sys_SITE_PART24_CONTRACT5.CONTRACT2DIR_SITEPART=sys_CONTRACT.OBJID)
INNER JOIN SA.TABLE_PART_NUM sys_PART_NUM ON sys_MOD_LEVEL.PART_INFO2PART_NUM=sys_PART_NUM.OBJID)
LEFT OUTER JOIN SA.MTM_SITE_PART63_BUS_ORG108 ON sys_SITE_PART.OBJID=SA.MTM_SITE_PART63_BUS_ORG108.system_pto2bus_org)
LEFT OUTER JOIN SA.TABLE_BUS_ORG team_BUS_ORG ON mtm_site_part63_bus_org108.bus_org2system_pto=team_BUS_ORG.OBJID

WHERE sys_SITE_PART.SERIAL_NO = '#SYSTEMID#'

Recommended Answers

All 3 Replies

Not to be rude, but wasn't this already answered on like 2 other forums?

It was asked on two forums, but the answers involving SQL were incorrect, and the one reply referencing the cf functions mentioned in this thread only said that I'd probably need to use the functions, which while it may have pointed me in the right direction, didn't have enough content to help. So I thought I'd broaden my search scope.

Some of the answers looked promising to me. Basically what I'd suggest. If they didn't work for you, did you ask the people that suggested them follow-up questions?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.