Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

MSSQL query dilema

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
aka_FATCAT
Asked:
aka_FATCAT
  • 4
  • 2
  • 2
2 Solutions
 
Pratima PharandeCommented:
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
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
SELECT CheckNumber, InvoiceDetails from InvoiceTable
INNER JOIN BankingTable ON
InvoiceTable.CheckNumber=BankingTable.CheckNumber

Open in new window

0
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Corrections...

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

Open in new window

0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
aka_FATCATAuthor Commented:
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
 
aka_FATCATAuthor Commented:
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
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now