Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sql Query issue

Posted on 2007-07-29
5
Medium Priority
?
232 Views
Last Modified: 2010-07-27
Hi

At the company I work for we use Sage software (whose database tables I cannot change)

One of these tables (sales_customer) has the following fields

customer_account_no
name
invoice_customer
group_customer

The group customer field holds a customer account number also. I want to do a query which selects all accounts  and group customer and that group customers name. I have tried the following:-

Select customer_account_no,name
from sales_customer
where group_customer=customer_account_no

This however only returns the customer_account_no and name of the current row and not the record that the group customer refers to.

ie. if my data was as follows

customer_account_no     name               invoice_customer   group_customer
A100                       Joe Smith            A102            A102
A102                       Joe Smith HQ             -            -
A103                       J Smith inc            A102            A102
A104                       Another Co.            -            -
A105                  Test co.            -            -
A106                  J Smith Ent            A102            A102


I would like returned

customer_account_no     name         
A102                       Joe Smith HQ       
A104                         Another Co.
A105                  Test Co.

Can this be done?

Thanks for any help.



this however only returns the  
0
Comment
Question by:sagarh
5 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19589540
SELECT customer_acoount_no, [name]
FROM SomeTable
WHERE invoice_customer IS NULL AND group_customer IS NULL
0
 

Author Comment

by:sagarh
ID: 19589617
Hi In some instances we might have invoice_customer and group_customer filled in but referring to itself ie.

customer_account_no       name               invoice_customer   group_customer
A100                                  Joe Smith            A102            A102
A102                                  Joe Smith HQ        A102          A102
A103                                  J Smith inc            A102            A102
A104                                  Another Co.          A104            A104
A105                                  Test co.                         -            -
A106                                  J Smith Ent            A102            A102

Sorry I should have mentioned this.
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 19589776
SELECT customer_acoount_no, [name]
FROM SomeTable
WHERE (invoice_customer IS NULL AND group_customer IS NULL) OR (invoice_customer=customer_account_no and group_customer=customer_account_no)

?
0
 

Author Comment

by:sagarh
ID: 19596107
HI - please ignore the null values - I over simplified my example and seemed to confused the issue.  

The issue essentially is that when I run this query:-

SELECT customer_acoount_no, [name]
FROM SomeTable
WHERE invoice_customer=customer_account_no and group_customer=customer_account_no

the column [name] that is returned is that of the current record not that of the group customer....hope this helps in clarifying this.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 19596148
You want *only* the group info returned?  OK:

SELECT st2.customer_account_no, st2.[name]
FROM SomeTable st
INNER JOIN SomeTable st2 ON st2.customer_account_no = st.group_customer

[I would have thought you might want the original acct#, then the group acct# and name, but that was just my best guess :-) .]
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

578 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