Sql Query issue

Posted on 2007-07-29
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 92

    Expert Comment

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

    Author Comment

    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

    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

    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 68

    Accepted Solution

    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

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now