Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-11-24
4
Medium Priority
?
459 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 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

877 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