Solved

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

Posted on 2006-11-24
4
444 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
  • 2
  • 2
4 Comments
 
LVL 142

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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.
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now