1

I need to get both table and here is the table structure

Table A

  • UserID
  • Username
  • Status
  • IntroCode

Table B

  • IntroCode
  • UserID

I want to get the table a data and join with table b on tblA.IntroCode = tblB.IntroCode, then get the username of tblB.userID. How can i do such join ?

I tried half way and stuck in the middle, please help. Thanks for reply

Community
  • 1
  • 1
1myb
  • 3,536
  • 12
  • 53
  • 73
  • Thx for effort of you all guys, responces is super fast =) and the correct answer is answer from @John Woo – 1myb Sep 26 '12 at 09:30

7 Answers7

6

This is just a simple join.

SELECT  a.*, b.*    -- select your desired columns here
FROM    tableA a
        INNER JOIN tableB b
            ON a.IntroCode = b.IntroCode
WHERE   b.userid = valueHere

UPDATE 1

SELECT  a.UserID, 
        a.`Username` OrigUserName,
        a.`Status`,
        c.`Username` IntroUserName
FROM    tableA a
        INNER JOIN tableB b
            ON a.IntroCode = b.IntroCode
        INNER JOIN tableA c
            ON b.userID = c.userID
-- WHERE b.UserID = valueHere       -- extra condition here
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • but there're 2 username. how can i do it ? one is the original data username and the other one is intro's username – 1myb Sep 26 '12 at 09:11
  • 2
    +1 for speed and accuracy - also a great opportunity to link @SLim to http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables which was written to further explain this sort of question and answer :) – Fluffeh Sep 26 '12 at 09:12
  • @SLim what do you mean by two username? anyway i just updated the answer. Maybe `where` clause can help you. – John Woo Sep 26 '12 at 09:12
  • Refer to them using the table prefix. Use any of the queries provided, and replace **B.userId** with **B.userId as userId** – Vlad Sep 26 '12 at 09:13
  • I don't understand and i guess you misunderstand? What i mean is i want to get the username of the row, and also i want to get the username or introguy. – 1myb Sep 26 '12 at 09:15
  • 1
    @SLim maybe I get your point, you need to add another join to get the username of the ID. i will update my answer. – John Woo Sep 26 '12 at 09:18
  • Exactly!! I need to get Username of TableA also i want to get Username owned by UserID in TableB – 1myb Sep 26 '12 at 09:20
  • @Slim did you check my answer... replied with the answer before – Bokw Sep 26 '12 at 09:24
  • @JohnWoo, Yea !!! I Got It !!!!, Mr. Bokw, Sorry.. i go through one by one and he is the first person gave the answer =( and i don't have the ID so i need to get all.. – 1myb Sep 26 '12 at 09:28
3
SELECT column_name(s)
FROM TableA
LEFT JOIN TableB
ON TableA.UserID=TableB.UserID
chokrijobs
  • 761
  • 1
  • 6
  • 10
2
SELECT B.userID from TableA A
LEFT JOIN TableB B on A.IntroCode=B.IntroCode
Vlad
  • 163
  • 8
2
select a.*,b.IntroCode  from TableA a left join TableB b
on a.IntroCode = b.IntroCode
AnandPhadke
  • 13,160
  • 5
  • 26
  • 33
2

you have to give the columns with same name an unique value:

SELECT  a.UserID as uid_a, b.UserID as uid_b
FROM    tableA a
INNER JOIN tableB b ON a.IntroCode = b.IntroCode
WHERE   b.UserID = 1
Bokw
  • 789
  • 1
  • 9
  • 21
2

Use this query.

 SELECT TableA.Username FROM TableA JOIN TableB ON (TableA.IntroCode = TableB.IntroCode);
Bhoopesh Pathak
  • 179
  • 1
  • 11
1

use this query

SELECT  *  FROM tblA INNER JOIN tblB ON tblA.IntroCode = tblB.IntroCode where tblB.userid = value
Yogesh Suthar
  • 30,424
  • 18
  • 72
  • 100