join 2 queries SQL + ColdFusion

Got two queries, and can't make them work properly, their end product gives the repeated items, here is the queries:

1st Query

<cfquery name="get_partner_all" datasource="#dsn#">
    SELECT  
        C.COMPANY_ID,
        C.FULLNAME,
        CP.MOBILTEL,
        CP.MOBIL_CODE, 
        CP.IMCAT_ID,
        CP.COMPANY_PARTNER_TEL, 
        CP.COMPANY_PARTNER_TELCODE,
        CP.COMPANY_PARTNER_TEL_EXT,
        CP.MISSION, 
        CP.DEPARTMENT, 
        CP.TITLE,
        CP.COMPANY_PARTNER_SURNAME, 
        CP.COMPANY_PARTNER_NAME, 
        CP.PARTNER_ID, 
        CP.COMPANY_PARTNER_EMAIL, 
        CP.HOMEPAGE, 
        CP.COUNTY,
        CP.COUNTRY,
        CP.COMPANY_PARTNER_ADDRESS, 
        CP.COMPANY_PARTNER_FAX,
        CP.RECORD_DATE,
        CP.MEMBER_CODE,
        CC.COMPANYCAT
        <cfif isdefined('attributes.report_sort') and attributes.report_sort is 2>
        ,CCD.SITE_DOMAIN
        </cfif>
    FROM
        COMPANY_PARTNER CP,
        COMPANY C,
        COMPANY_CAT CC
        <cfif isdefined('attributes.report_sort') and attributes.report_sort is 2>
        ,COMPANY_CONSUMER_DOMAINS CCD
        </cfif>
    WHERE
        C.COMPANY_ID = CP.COMPANY_ID
        AND C.COMPANYCAT_ID = CC.COMPANYCAT_ID      
    <cfif isDefined('attributes.search_status') and len(attributes.search_status)> 
        AND CP.COMPANY_PARTNER_STATUS = <cfqueryparam cfsqltype="cf_sql_integer" value="#attributes.search_status#">
    </cfif>
    <cfelseif isDefined("attributes.comp_cat") and len(attributes.comp_cat)>
        AND C.COMPANYCAT_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#attributes.comp_cat#">
    </cfif>
    <cfif isdefined('attributes.report_sort') and attributes.report_sort is 2>
        AND CP.PARTNER_ID = CCD.PARTNER_ID
        AND CCD.SITE_DOMAIN = 'www.projedepo.com'
    </cfif>
</cfquery>

2nd Query:

<cfquery name="get_money" datasource="#dsn2#">
    SELECT BAKIYE,BORC,ALACAK FROM COMPANY_REMAINDER_MONEY WHERE COMPANY_ID = #company_id#
</cfquery>

when i try to define the for example C.COMPANY_ID=CRM.COMPANY_ID it shows some repeated items, i know that i should use smth like JOIN and other stuff, but can't understand how to do it :) need help, and thank you all for help!

here's the joined query, still repeated variables:

<cfquery name="get_partner_all" datasource="#dsn#">
    SELECT DISTINCT
        C.COMPANY_ID,
        C.FULLNAME,
        CP.MOBILTEL,
        CP.MOBIL_CODE, 
        CP.IMCAT_ID,
        CP.COMPANY_PARTNER_TEL, 
        CP.COMPANY_PARTNER_TELCODE,
        CP.COMPANY_PARTNER_TEL_EXT,
        CP.MISSION, 
        CP.DEPARTMENT, 
        CP.TITLE,
        CP.COMPANY_PARTNER_SURNAME, 
        CP.COMPANY_PARTNER_NAME, 
        CP.PARTNER_ID, 
        CP.COMPANY_PARTNER_EMAIL, 
        CP.HOMEPAGE, 
        CP.COUNTY,
        CP.COUNTRY,
        CP.COMPANY_PARTNER_ADDRESS, 
        CP.COMPANY_PARTNER_FAX,
        CP.RECORD_DATE,
        CP.MEMBER_CODE,
        CC.COMPANYCAT,
        CRM.*
        <cfif isdefined('attributes.report_sort') and attributes.report_sort is 2>
        ,CCD.SITE_DOMAIN
        </cfif>
    FROM
        COMPANY_PARTNER CP,
        COMPANY C,
        COMPANY_CAT CC,
        #dsn2_alias#.COMPANY_REMAINDER_MONEY CRM
        <cfif isdefined('attributes.report_sort') and attributes.report_sort is 2>
        ,COMPANY_CONSUMER_DOMAINS CCD
        </cfif>
    WHERE
        C.COMPANY_ID = CP.COMPANY_ID
        AND C.COMPANYCAT_ID = CC.COMPANYCAT_ID
        AND C.COMPANY_ID=CRM.COMPANY_ID 
    <cfif isDefined('attributes.search_status') and len(attributes.search_status)> 
        AND CP.COMPANY_PARTNER_STATUS = <cfqueryparam cfsqltype="cf_sql_integer" value="#attributes.search_status#">
    开发者_开发问答</cfif>     
    <cfif isDefined("attributes.cpid") and len(attributes.cpid)>
        AND C.COMPANY_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#attributes.cpid#">
    <cfelseif isDefined("attributes.comp_cat") and len(attributes.comp_cat)>
        AND C.COMPANYCAT_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#attributes.comp_cat#">
    </cfif>
    <cfif isdefined('attributes.report_sort') and attributes.report_sort is 2>
        AND CP.PARTNER_ID = CCD.PARTNER_ID
        AND CCD.SITE_DOMAIN = 'www.projedepo.com'
    </cfif>
</cfquery>

The query is not wrong, it just shows all (valid) combinations of COMPANY_PARTNERs and COMPANY_CONSUMER_DOMAINS. if you want that, you can add an ORDER BY in the end:

ORDER BY C.COMPANY_ID,
         CP.PARTNER_ID,
         CRM.DOMAINID          --- some field in table CRM

or

ORDER BY C.COMPANY_ID,
         CRM.DOMAINID          --- some field in table CRM
         CP.PARTNER_ID,

or whichever other ordering you like, say:

ORDER BY C.COMPANY_NAME
         C.COMPANY_ID,
         CP.COUNTRY,
         CP.COUNTY,
         CP.TITLE,
         CP.PARTNER_ID,

I see two more ways where this query can show (other possibly interesting) data:

1 Change (in the SELECT list) CRM.* into Count(*) AS ConsumerDomainsCount

and add a GROUP BY CP.COMPANY_ID, CP.PARTNER_ID at the end.

2 Mirror operation, GROUP BY CRM.COMPANY_ID, CRM.DOMAINID and change the SELECT list by removing any CP.* and keep only Count(*) AS PartnersCount

If you use GROUP BY, you could also use other aggregate functions, like SUM(), AVG(), MIN(), MAX() if you have fields that can be added (numer fields) or compared (like dates).

Is this as simple as using the DISTINCT keyword?

<cfquery name="get_money" datasource="#dsn2#">
    SELECT DISTINCT COL1, COL2 ... FROM ...
</cfquery>

Also - you should use <cfqueryparam /> to avoid SQL injection vulnerabilities.