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


Sql Query issue

Posted on 2007-07-29
Medium Priority
Last Modified: 2010-07-27

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  
Question by:sagarh
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

Author Comment

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.
LVL 15

Expert Comment

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)


Author Comment

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.
LVL 70

Accepted Solution

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 :-) .]

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