[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Join Multiple Tables

Posted on 2011-03-11
13
Medium Priority
?
353 Views
Last Modified: 2012-05-11
I'm getting this error.

The multi-part identifier "invoice_payments.inv_id" could not be bound.

Need help joining these tables together.

Please assist.
<cfquery name="invoices" datasource="#request.dataSource#">
SELECT *
FROM invoice, vendor, invoice_payments, payment_type, payments
LEFT OUTER JOIN invoice ON invoice.invoice_id = invoice_payments.inv_id
LEFT OUTER JOIN payments ON payments.p_id = invoice_payments.p_id
WHERE vendor.vid = invoice.vid 
<cfif isDefined ('URL.state')>
AND invoice_status = '#URL.state#'
</cfif>
<cfif isDefined ('URL.vid')>
AND invoice.vid = '#URL.vid#'
</cfif>
ORDER BY vendor.v_name, invoice_date  
</cfquery>

Open in new window

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
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35107662
try:-

SELECT *
FROM  invoice_payments
LEFT OUTER JOIN invoice ON invoice.invoice_id = invoice_payments.inv_id
LEFT OUTER JOIN payments ON payments.p_id = invoice_payments.p_id
inner join vendor on vendor.vid = invoice.vid 

Open in new window


0
 
LVL 8

Expert Comment

by:pdd1lan
ID: 35107670
is the field name "inv_id" in other tables beside "Invoice_payments" table?

my suggest is not to use SELECT *, if you have a small database, it might not make different, but if you have a large database, display all the fields in all your join tables, perform might be slow.  

SELECT invoice_payments.inv_id,payments.p_id ,....
FROM invoice, vendor, invoice_payments, payment_type, payments
....
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35107675
no join condition is provided for table payment_type
therefore i ve not included that in query.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:aka_FATCAT
ID: 35107747
I'm still getting the same error message.
<cfquery name="invoices" datasource="#request.dataSource#">
SELECT invoice_number, v_name, payment_date, payment_amount
FROM invoice, vendor, invoice_payments, payment_type, payments
LEFT OUTER JOIN invoice ON invoice.invoice_id = invoice_payments.inv_id
LEFT OUTER JOIN payments ON payments.p_id = invoice_payments.p_id
LEFT OUTER JOIN payment_type ON payment_type.pt_id = payments.payment_type
INNER JOIN vendor ON vendor.vid = invoice.vid 
<cfif isDefined ('URL.state')>
AND invoice_status = '#URL.state#'
</cfif>
<cfif isDefined ('URL.vid')>
AND invoice.vid = '#URL.vid#'
</cfif>
ORDER BY vendor.v_name, invoice_date  
</cfquery>

Open in new window

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 35107797
your FROM part is wrong...
<cfquery name="invoices" datasource="#request.dataSource#">
SELECT invoice_number, v_name, payment_date, payment_amount
FROM invoice 
INNER JOIN vendor ON vendor.vid = invoice.vid 
LEFT OUTER JOIN invoice_payments ON invoice.invoice_id = invoice_payments.inv_id
LEFT OUTER JOIN payments ON payments.p_id = invoice_payments.p_id
LEFT OUTER JOIN payment_type ON payment_type.pt_id = payments.payment_type
<cfif isDefined ('URL.state')>
AND invoice_status = '#URL.state#'
</cfif>
<cfif isDefined ('URL.vid')>
AND invoice.vid = '#URL.vid#'
</cfif>
ORDER BY vendor.v_name, invoice_date  
</cfquery>

Open in new window

0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35107815
why are you using table name for more than once?

try:-

SELECT invoice_number, v_name, payment_date, payment_amount
FROM invoice_payments
LEFT OUTER JOIN invoice ON invoice.invoice_id = invoice_payments.inv_id
LEFT OUTER JOIN payments ON payments.p_id = invoice_payments.p_id
LEFT OUTER JOIN payment_type ON payment_type.pt_id = payments.payment_type
INNER JOIN vendor ON vendor.vid = invoice.vid 

Open in new window

0
 
LVL 8

Expert Comment

by:pdd1lan
ID: 35108690
can you try it without "ORDER BY"?

<cfquery name="invoices" datasource="#request.dataSource#">
SELECT *
FROM invoice, vendor, invoice_payments, payment_type, payments
LEFT OUTER JOIN invoice ON invoice.invoice_id = invoice_payments.inv_id
LEFT OUTER JOIN payments ON payments.p_id = invoice_payments.p_id
WHERE vendor.vid = invoice.vid
<cfif isDefined ('URL.state')>
AND invoice_status = '#URL.state#'
</cfif>
<cfif isDefined ('URL.vid')>
AND invoice.vid = '#URL.vid#'
</cfif>
</cfquery>
0
 
LVL 6

Expert Comment

by:billfusion
ID: 35109542
This may sound like a silly question but I have to ask, are you sure the invoice id field in invoice_payment is inv_id not something else.  I just recreated the tables based on your query and it worked OK for me.  Just to be sure, use the field and table names that are not working exactly including case sensitivity.

Here is the query I used:

SELECT     invoice.invoice_number, vendor.v_name, payments.payment_date, payments.payment_amount
FROM         invoice CROSS JOIN
                      vendor CROSS JOIN
                      invoice_payments CROSS JOIN
                      payment_type CROSS JOIN
                      payments LEFT OUTER JOIN
                      invoice invoice_1 ON invoice.invoice_id = invoice_payments.inv_id LEFT OUTER JOIN
                      payments payments_1 ON payments.p_id = invoice_payments.p_id LEFT OUTER JOIN
                      payment_type payment_type_1 ON payment_type.pt_id = payments.payment_type INNER JOIN
                      vendor vendor_1 ON vendor.vid = invoice.vid AND invoice.invoice_status = 1 AND invoice.vid = 1
ORDER BY vendor.v_name, invoice.invoice_date
0
 
LVL 1

Expert Comment

by:vandalesm
ID: 35109763
Try putting an table alias in every table of your query.

The error might be that you have column name called "invoice_paments" which conflicts the name of your table.
0
 

Author Comment

by:aka_FATCAT
ID: 35112404
angelIII, your code is working great.

The only part that isn't working is the <cfif> part. It doesn't work and it doesn't report any errors.
<cfquery name="invoices" datasource="#request.dataSource#">
SELECT invoice_number, v_name, payment_date, payment_amount, invoice_amount, invoice.vid, invoice_date, check_number, payment_type_name, invoice_status, invoice_id
FROM invoice 
INNER JOIN vendor ON vendor.vid = invoice.vid 
LEFT OUTER JOIN invoice_payments ON invoice.invoice_id = invoice_payments.inv_id
LEFT OUTER JOIN payments ON payments.p_id = invoice_payments.p_id
LEFT OUTER JOIN payment_type ON payment_type.pt_id = payments.pt_id
<cfif isDefined ('URL.state')>
AND invoice_status = '#URL.state#'
</cfif>
<cfif isDefined ('URL.vid')>
AND invoice.vid = '#URL.vid#'
</cfif>
ORDER BY vendor.v_name, invoice_date  
</cfquery>

Open in new window

0
 
LVL 6

Expert Comment

by:billfusion
ID: 35113610
So, you have a url variable named vid or state and the query does no filter those values?
0
 
LVL 6

Accepted Solution

by:
billfusion earned 1000 total points
ID: 35113618
continuing my last comment.  Those statements may fit better in the where clause:


<cfquery name="invoices" datasource="#request.dataSource#">
SELECT invoice_number, v_name, payment_date, payment_amount, invoice_amount, invoice.vid, invoice_date, check_number, payment_type_name, invoice_status, invoice_id
FROM invoice
INNER JOIN vendor ON vendor.vid = invoice.vid
LEFT OUTER JOIN invoice_payments ON invoice.invoice_id = invoice_payments.inv_id
LEFT OUTER JOIN payments ON payments.p_id = invoice_payments.p_id
LEFT OUTER JOIN payment_type ON payment_type.pt_id = payments.pt_id
Where
<cfif isDefined ('URL.state')>
AND invoice_status = '#URL.state#'
</cfif>
<cfif isDefined ('URL.vid')>
AND invoice.vid = '#URL.vid#'
</cfif>
ORDER BY vendor.v_name, invoice_date  
</cfquery>
0
 

Author Closing Comment

by:aka_FATCAT
ID: 35113717
Thanks for all your help guys.
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

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…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

649 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