?
Solved

INNER JOIN query with same ID to multiple columns in another table

Posted on 2006-11-24
4
Medium Priority
?
453 Views
Last Modified: 2013-12-24
I have a table set up where a customer can ask another customer a question.  The customer_id must match 2 columns in the questions table.  In my questions table i have a column for question_from, and another column for question_to.  These columns are tied to the customer_id column in the customers table.  

I can't seem to correctly query all questions and tie the columns in displaying the username of the customer (customers.username)...here is my query.  I don't have a problem if i just output question_from and question_to, but these are ID's and i want to output the username of the customer.

THIS GIVES ME NO RECORDS
------------------------------------------------
<cfquery name="getquestions" datasource="#dsn#">
select questions.question_id, questions.question_from, questions.question_to, customers.customer_id, customers.username
from questions, customers
where customers.customer_id = questions.question_from
and customers.customer_id = questions.question_to
order by questions.question_id DESC
</cfquery>


THIS GIVES ME A SQL ERROR:
Tables or functions 'customers' and 'customers' have the same exposed names. Use correlation names to distinguish them.
------------------------------------------------
<cfquery name="getquestions" datasource="#dsn#">
select questions.question_id, questions.question_from, questions.question_to, customers.customer_id, customers.username
from questions, customers
where customers.customer_id = questions.question_from
and customers.customer_id = questions.question_to
order by questions.question_id DESC
</cfquery>

MY GOAL IS TO OUTPUT THE QUERY BUT OUTPUT THE USERNAME.  SINCE THERE IS ONLY ONE USERNAME FIELD  HOW CAN I ACCOMPLISH THIS?

MAYBE IT'S A DIFFERENT JOIN I'M UNAWARE OF?  THANKS
0
Comment
Question by:tags266
[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
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18007950
<cfquery name="getquestions" datasource="#dsn#">
select q.question_id, q.question_from, q.question_to, f.username as from_name, t.username as to_name
from questions q
join customers f
 on f.customer_id = q.question_from
join customers t
and t.customer_id = q.question_to
order by q.question_id DESC
</cfquery>
0
 

Author Comment

by:tags266
ID: 18008062
angelIII, thanks for the quick response but i'm getting a SQL error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'and'.

also,
what variable will i use to output the to and from field so i get the username and not the ID?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18008108
sorry, copy/paste error, should be ON instead of AND

<cfquery name="getquestions" datasource="#dsn#">
select q.question_id, q.question_from, q.question_to, f.username as from_name, t.username as to_name
from questions q
join customers f
 on f.customer_id = q.question_from
join customers t
 on t.customer_id = q.question_to
order by q.question_id DESC
</cfquery>

>what variable will i use to output the to and from field so i get the username and not the ID?
I aliased the field from_name and to_name
0
 

Author Comment

by:tags266
ID: 18008172
There we go.  Thanks.. never had to alias a column table like that before.  now i've learned something new thanks to you!  Great job.

-Tags266
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
What You Need to Know when Searching for a Webhost Provider
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

719 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