Solved

Need help with a "Join" script

Posted on 2008-06-25
8
214 Views
Last Modified: 2012-05-05
Trying to get info from from two tables using a join. Tables are Billing Header and Billing Detail. From billing header I want "billing" and "primary code" from Billing Detail I want "Service Date 1". Problem is.. in Billing Header there is one record per bill, in Billing Detail there can me multiple records per bill depending on how many line items were on the bill but the service date is the same for the entire bill. How do I script the query? There are two common colunms in each table, Chart and Billing.
0
Comment
Question by:wdabbs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21868043
try this:

select b.billing, b.primarycode, bh.servicedate1 from billingheader b
join billingdetail bh on b.chart = bh.chart and b.billing = bh.billing
0
 

Author Comment

by:wdabbs
ID: 21868275
select Billing Header.Billing, Billing Header.Primary Code, Billing Detail.Service Date 1 from b
join Billing Detail bh on b.chart = bh.chart and Billing Detail.billing = Billing Header.billing   <- is this how it should look?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21868301
no quite.  you'll need brackets around the table names if they are two separate words:

select b.Billing, b.Primary Code, bhService Date 1
from [Billing Header] b
join [Billing Detail] bh on b.chart = bh.chart and bh.billing = b.billing  
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:wdabbs
ID: 21868408
Getting select b.Billing, b.Primary Code, bhService Date 1
from [Billing Header] b
join [Billing Detail] bh on b.chart = bh.chart and bh.billing = b.billing
*** ERROR: Incorrect syntax near the keyword 'Primary'. ***

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21868418
whoops...missed that one

select b.Billing, b.[Primary Code], bh.[Service Date 1]
from [Billing Header] b
join [Billing Detail] bh on b.chart = bh.chart and bh.billing = b.billing  
0
 

Author Comment

by:wdabbs
ID: 21868593
Thank you sir! You are most kind and generous with your knowledge :) So the need to "Billing Header" b and "Billing Detail" bh? is that just to adreviate?? and what is operator "on", what's is saying??
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21868621
On is part of the ANSI join statement I used in the query.  As for b and bh, I used them as table aliases.
0
 

Author Closing Comment

by:wdabbs
ID: 31470675
Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

691 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