Solved

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

Posted on 2006-11-24
4
448 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 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 Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

710 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