Help with a query.

[jrottman]

I am working on a search screen, in the returned results, I have one column that I need to return where the results for that column are combined. IE if I have two returned values for the column partyName, I need them to show up as one record, instead of two. Here is the query that I am currently using.

SELECT
tbl_smartPanel_propInfo_Sale.fld_fileNum,
tbl_smartPanel_propInfo_Sale.fld_mlsNum,
tbl_smartPanel_propInfo_Sale.fld_propStatus,
tbl_smartPanel_propInfo_Sale.fld_propAddress,
tbl_smartPanel_propInfo_Sale.fld_propCoe,
tbl_smartPanel_propInfo_Sale.fld_propPrice,
tbl_smartPanel_propInfo_Sale_Parties.fld_partyType,
tbl_smartPanel_propInfo_Sale_Parties.fld_partyName
FROM
tbl_smartPanel_propInfo_Sale
Inner Join tbl_smartPanel_propInfo_Sale_Parties ON tbl_smartPanel_propInfo_Sale.fld_fileNum = tbl_smartPanel_propInfo_Sale_Parties.fld_fileNum
WHERE
tbl_smartPanel_propInfo_Sale.fld_PropStreet = 'Baseline' AND
tbl_smartPanel_propInfo_Sale_Parties.fld_partyType = 'HomeSmart'

 

[r937]

often when asking a question relating to SQL, it will help us a great deal if you would kindly mention which database you're using

if you're using mysql, there is a wonderful function that will do what you want right in the SQL --

select S.fld_fileNum
     , S.fld_mlsNum
     , S.fld_propStatus
     , S.fld_propAddress
     , S.fld_propCoe
     , S.fld_propPrice
     , SP.fld_partyType
     , group_concat(SP.fld_partyName) as partynames
  from tbl_smartPanel_propInfo_Sale as S
inner 
  join tbl_smartPanel_propInfo_Sale_Parties as SP
    on SP.fld_fileNum = S.fld_fileNum
   and SP.fld_partyType = 'HomeSmart'
 where S.fld_PropStreet = 'Baseline' 
group
    by S.fld_fileNum
     , S.fld_mlsNum
     , S.fld_propStatus
     , S.fld_propAddress
     , S.fld_propCoe
     , S.fld_propPrice
     , SP.fld_partyType

otherwise, it is probably simplest if you use coldfusion's GROUP= parameter --

select S.fld_fileNum
     , S.fld_mlsNum
     , S.fld_propStatus
     , S.fld_propAddress
     , S.fld_propCoe
     , S.fld_propPrice
     , SP.fld_partyType
     , SP.fld_partyName
  from tbl_smartPanel_propInfo_Sale as S
inner
  join tbl_smartPanel_propInfo_Sale_Parties as SP
    on SP.fld_fileNum = S.fld_fileNum
   and SP.fld_partyType = 'HomeSmart'
 where S.fld_PropStreet = 'Baseline'
order
    by S.fld_fileNum

<cfoutput query="queryname" group="fileNum">
#fld_fileNum#
#fld_mlsNum#
#fld_propStatus#
#fld_propAddress#
#fld_propCoe#
#fld_propPrice#
#fld_partyType#
<cfset partynames=''>
<cfoutput>
<cfset partynames 
   = ListAppend(partynames,fld_partyName)>
</cfoutput>   
#partynames#
</cfoutput>

r937.com | rudy.ca