Solved

MSSQL query dilema

Posted on 2011-03-16
8
244 Views
Last Modified: 2012-05-11
I have two tables.

invoice & banking

Under my banking table I have check number, and under invoice table, I have the same check number.

I need it to return all my check details and include the invoice number(s) as well.

So my query returns all the checks I have written just fine, but I need it to also show me which invoice(s) I have it written for.

One check may have paid several invoices.

Thanks
0
Comment
Question by:aka_FATCAT
[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
  • 2
  • 2
8 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35153795
you must be geeting check information only form banking table

to get invoices get by joining to invoices table

something like this

select b.checknumber , i.invoicenumber form banking b
inner join invoices i on b.checknumber = i.checknumber
and b.acountnuber =12345

chngae the data as per your requirement

or share your query and field details
0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 35153806
SELECT CheckNumber, InvoiceDetails from InvoiceTable
INNER JOIN BankingTable ON
InvoiceTable.CheckNumber=BankingTable.CheckNumber

Open in new window

0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 35153809
Corrections...

 
SELECT CheckNumber, InvoiceDetails from InvoiceTable
INNER JOIN BankingTable ON
InvoiceTable.CheckNumber=BankingTable.CheckNumber
GROUP BY InvoiceTable.CheckNumber

Open in new window

0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 

Author Comment

by:aka_FATCAT
ID: 35153824
Its returning the data line by line.

What i want returned on the same line is,

check_number - pay_to - amount - invoice_number1, invoice_number2, invoice_number3, check_date
0
 

Author Comment

by:aka_FATCAT
ID: 35153839
Column 'Invoice.invoice_number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.




SELECT i.Check_Number, i.Invoice_number 
from Invoice i 
LEFT OUTER JOIN Banking b ON i.Check_Number=b.Check_Number
GROUP BY i.Check_Number

Open in new window

0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 35153856
Apology... corrections...

SELECT i.Check_Number, i.Invoice_number
from Invoice i
LEFT OUTER JOIN Banking b ON i.Check_Number=b.Check_Number
ORDER BY i.Check_Number
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 250 total points
ID: 35153863
0
 
LVL 19

Assisted Solution

by:Rikin Shah
Rikin Shah earned 250 total points
ID: 35153870
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

752 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