Solved

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

Posted on 2006-11-24
4
450 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

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
What You Need to Know when Searching for a Webhost Provider
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

623 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