troubleshooting Question

Sql Query issue

Avatar of sagarh
sagarhFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerSQL
5 Comments1 Solution248 ViewsLast Modified:

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


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  
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros