asked on

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.

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

swansonplace

ASKER

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

swansonplace

ASKER

Thank you for your help.