Query to join on account numbers

Billing and Shipping Account numbers

Page 1 of 1

3 Replies - 1515 Views - Last Post: 20 October 2006 - 10:40 AM Rate Topic: -----

#1 tody4me  Icon User is offline

  • Banned
  • member icon

Reputation: 12
  • View blog
  • Posts: 1,398
  • Joined: 12-April 06

Query to join on account numbers

Posted 20 October 2006 - 09:00 AM

Just wondering if someone would be able to help me out with a join on account numbers. What I have so far is a database full of part numbers, old and new, and now I need to be able to join on Account numbers, billing and shipping, which are usually not the same. The query that I wrote only works when the billing and shipping account number are the same, which in reality doesn't happen all that much.

Tables
Table1
[ID]
[BillingAccount]
[ShippingAccount]

Table2
[AccountID]
[AccountAddress]
[AccountZip]

Table3
[Zip]
[State]
[City]

query
join table1 on table2 where billingaccountnumber = accountid and shippingaccount = accountid join table3 where table2.accountzip = table3.zip

this is to bring in the address information for all of the different account numbers. I think that the problem is with the AND, but when I change it to OR, I get all records duplicated, because it's bringing in the information for billing on one record, and the information on shipping on the duplicated record. Is there a way to bring in the information into one record, so that I can get the information from that record. This does work when the account numbers are the same for billing and shipping.

Thanks for any help that you could offer.

Is This A Good Question/Topic? 0
  • +

Replies To: Query to join on account numbers

#2 rockstar_  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 32
  • View blog
  • Posts: 189
  • Joined: 16-October 06

Re: Query to join on account numbers

Posted 20 October 2006 - 10:18 AM

View Posttody4me, on 20 Oct, 2006 - 09:00 AM, said:

query
join table1 on table2 where billingaccountnumber = accountid and shippingaccount = accountid
join table3 where table2.accountzip = table3.zip


Yeesh, if this doesn't error out, I'd be surprised if you actually get ALL the fields you want. I'd suggest splitting retrieval of billing account and shipping account addresses into two different queries. Either that, or you'll need to alias fields, and those queries can get hard to follow (think of the dude that comes two developers after you...even if it is a personal project)

So for Billing Query, try this:

SELECT * FROM Table1 
JOIN Table2 ON Table1.BillingAccount=Table2.AccountID 
JOIN Table3 On Table2.AccountZIP=Table3.zip;


And for Shipping Query:
SELECT * FROM Table1 
JOIN Table2 ON Table1.ShippingAccount=Table2.AccountID 
JOIN Table3 On Table2.AccountZIP=Table3.zip;


I've attached a diagram that I've drawn of your database as you have described it. Am I understanding this correctly?

rockstar_

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#3 tody4me  Icon User is offline

  • Banned
  • member icon

Reputation: 12
  • View blog
  • Posts: 1,398
  • Joined: 12-April 06

Re: Query to join on account numbers

Posted 20 October 2006 - 10:26 AM

The diagram that you drew is accurate. That's how I have it set up right now, and how I wanted to query to work. I do have it aliased for Billing Account and Shipping Account. Each of the fields that I have joined based on Billing Account are "BillingAccount" + Field name, and Shiptoaccount + field name. I was thinking that I might have to have 2 different queries to do what I wanted, but then I make more work for the program to have to get 2 different record sets, which should work, but I wanted to bring it all into the same query. I could just bring in the info specific to the parts in one query, then the accounts into a different stored procedure, give me account number, i give you account info kind of logic.
Was This Post Helpful? 0
  • +
  • -

#4 rockstar_  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 32
  • View blog
  • Posts: 189
  • Joined: 16-October 06

Re: Query to join on account numbers

Posted 20 October 2006 - 10:40 AM

View Posttody4me, on 20 Oct, 2006 - 10:26 AM, said:

I was thinking that I might have to have 2 different queries to do what I wanted, but then I make more work for the program to have to get 2 different record sets, which should work, but I wanted to bring it all into the same query. I could just bring in the info specific to the parts in one query, then the accounts into a different stored procedure, give me account number, i give you account info kind of logic.

You always want your queries to be specific. You don't need to try and grab all fields under God's green earth in one query. Although it's a good effort, you complicate the matter by trying to get the parts, and the addresses, and the zips, and your mom all in one query. It's okay to have two or three specific queries run for each view.

Separating your queries means that you can optimize each query, and I bet you'll find a speed difference when you have three tight queries instead of one HUGE one.

rockstar_
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1