How to retrieve sql data?

I am trying to perform a select on the below code, but I am not performing the select correctly.  Could you let me know what the query should look like?

Company --> Company.Code
Company_Rules --> Company_Rules.Company

Sample Input Data

Company                
Code
1
2
3

Company Rules
Company                      Rule Code
1                                        100              
1                                        200
3                                        101

Desired Output
Code                      company
1                                1
2                              Null
3                                3

In essence I want all of the company data that that with the matching company_rules data.  But the resulting output does not return multiple rows of company_rules.  The data that does not find a matching company_rules field is also return.

swansonplaceAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT  C.Code, Min (R.RuleCode) RuleCode
FROM Company c
LEFT OUTER JOIN CompanyRules R
ON C.Code = R.Code
GROUP BY C.Code
0
 
swansonplaceAuthor Commented:
The above statement works for the data given.  I tried to narrow down the issue, and I left out data.

In essence I want all of the company data that that with the matching company_rules data.  But the resulting output does not return multiple rows of company_rules.  The data that does not find a matching company_rules field is also return.  

Also the additional fields for each company is needed.

My sql statement:
select company.billingtype, company.modifytime as modifieddate, company.code, company.name as coname, company.city + ' ' + company.state + ' ' + company.zipcode as CoAddress, Min (R.RuleCode) RuleCode from Company left outer join company_rules R on company.code = R.company where len(ltrim(ltrim(company.code))) > 0 Group By company.Code ORDER BY coname

Company  each Code is distinct or unique        
Code   Additional Fields(Billingtype, ModifyTime, Name, CoAddress)
1          Billing Type, Modifytime, Name of Company, Company Address
2          Billing Type, Modifytime, Name of Company, Company Address
3          Billing Type, Modifytime, Name of Company, Company Address

Company Rules is the table with many company fields
Company                      Rule Code
1                                        100              
1                                        200
3                                        101

Desired Output
Code                      company    Additional Fields From Company (Billingtype, ModifyTime, Name,
                                                                                                      CoAddress)
1                                1             Additional Fields From Company
2                              Null           Additional Fields From Company
3                                3             Additional Fields From Company

0
 
swansonplaceAuthor Commented:
Thank you for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.