?
Solved

How to retrieve sql data?

Posted on 2007-07-31
3
Medium Priority
?
205 Views
Last Modified: 2010-03-20
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.

0
Comment
Question by:swansonplace
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 19600422
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
 

Author Comment

by:swansonplace
ID: 19600894
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
 

Author Comment

by:swansonplace
ID: 19601593
Thank you for your help.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question