Inner Join MYSQL query

Question

0.00/5 (No votes)

See more:

Hi Everyone,

I'm wondering if any of you can help me here...

I have two tables:

Table 1 Columns:

|Event ID|User ID|Event Name|
------------------------------

Table 2 Columns:

|User ID|Username|Password|Display Name|
------------------------------------------

Basically I'm trying to select everything from Table 1, but in the results, I need to show the Username rather than the User ID, and I know I'd have to use an INNER JOIN query to select the Username from Table 2.

I just can't seem to get the syntax right...

Regards,
Tom.

Posted 27-Aug-12 23:00pm

thomasriley

Add a Solution

3 solutions

Solution 2

No Issues!!!

SQL

Select Table1.EventID, Table1.EventName, Table2.UserName from Table1 Table1 Inner join Table2 Table2 on Table1.UserID = Table2.UserID

Regards
~Karthik~

Posted 27-Aug-12 23:23pm

Karthik J, Coimbatore

Updated 27-Aug-12 23:36pm

v2

Comments

thomasriley 28-Aug-12 5:34am

I'm doing that and I'm getting a MYSQL syntax error:

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.username FROM events events INNER JOIN tbl_users tbl_users ON (events.user_' at line 1 */

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

bhagirathimfs · Accepted Answer · 2012-08-27T23:42:00

Solution 3

If you don't want to use alias name than try this.

SQL

SELECT TABLE1.EVENTID, TABLE1.EVENTNAME, TABLE2.USERNAME FROM TABLE1  
INNER JOIN TABLE2 ON TABLE1.USERID = TABLE2.USERID;

Suggestion:
It is better to use alias name so that there is no need of write the whole table name.

Posted 27-Aug-12 23:42pm

bhagirathimfs

Dhol Gaurav · Accepted Answer · 2012-08-27T23:11:00

Solution 1

Hello

It's so simple, check below query it will help to you

SQL

Select T1.EventID, T1.EventName, T2.UserName from Table1 T1 Inner join Table2 t2 on T1.UserID = T2.UserID

also find more about the join into below links

http://www.w3schools.com/sql/sql_join.asp[^]

Thanks
Gaurav Dhol

Posted 27-Aug-12 23:11pm

Dhol Gaurav

Comments

thomasriley 28-Aug-12 5:15am

Hi Dhol and thankyou for your reply, at the FROM part of your answer, you have put "Table 1 T1". What do you mean by "T1"? Or would I put the first table name twice?

bhagirathimfs 28-Aug-12 5:30am

Here T1 is the alias name of table one[Table1].
If you remove that alias names that it will show error that
"user_id in field list is ambiguous"
because the compiler can't distinguish between the two table's user_id.

So alias name is give to each table.

bhagirathimfs 28-Aug-12 5:31am

Go through the give link by MR.Dhol. It is a very good site to learn SQL join

Dhol Gaurav 28-Aug-12 5:58am

Thanks bhagirathimfs

Dhol Gaurav 28-Aug-12 6:00am

thomasriley please go throw w2school link, it is a very good tutorial with example, after complication of tutorial you are able to resolve this type of problem by your self

thomasriley 29-Aug-12 6:54am

Thankyou Dhol Gaurav your solution worked for me, I had just misread it. Apologies.