MySQL Query with Multiple Tables

Question

Table: UserInfoTbl
=======================
| username  | type    |
=======================
| user0001  | premium |
| user0002  | premium |
| user0003  | normal  |
| user0004  | premium |
=======================

Table: UserPvTbl
========================
| username | fUsername |
========================
| user0003 | user0002  |
| user0002 | user0001  |
| user0003 | user0001  |
========================

How can I select all the information from UserInfoTbl where fUsername of UserPvTbl has username of user0003?

Edit: Meaning that I need to retrieve information of user0002 and user0001 from the UserInfoTbl

Edit2: Relationship between both tables : UserInfoTbl.username = UserPvTbl.username

jammin · Accepted Answer · 2012-03-09 06:35:05Z

8

DroidMatt can you clarify what the 2 tables relationship are

UserInfoTbl.username = UserPvTbl.fusername
or
UserInfoTbl.username = UserPvTbl.username

Vikram is right assuming the first. otherwise you want this.

SELECT *
FROM UserInfoTbl, UserPvTbl
WHERE UserPvTbl.username = UserInfoTbl.username
AND UserPvTbl.username = 'user0003'

edited Mar 9, 2012 at 6:35

answered Mar 9, 2012 at 6:13

jammin

1044 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DroidMatt Over a year ago

UserInfoTbl.username = UserPvTbl.username is the correct one. Sorry I forgot to clarify the relationship.

jammin Over a year ago

Note: Vikram just renamed/shortened the table names. UserInfoTbl = U and UserPvTbl = UP

Abimaran Kugathasan · Accepted Answer · 2012-03-09 11:01:23Z

1

Use like :

select * from UserInfoTbl inner join UserPvTbl on UserInfoTbl.username=UserPvTbl.fusename
where UserPvTbl.usename='user0003'

edited Mar 9, 2012 at 11:01

Abimaran Kugathasan

32.7k11 gold badges81 silver badges109 bronze badges

answered Mar 9, 2012 at 6:14

khushbu

2221 gold badge3 silver badges11 bronze badges

Comments

Vikram · Accepted Answer · 2012-03-09 06:02:00Z

0

select U.*
from UserInfoTbl U
inner join UserPvTbl UP
on U.username  = UP.fusername  
where UP.username = 'user0003'

answered Mar 9, 2012 at 6:02

Vikram

8,32335 silver badges47 bronze badges

2 Comments

DroidMatt Over a year ago

Hello Vikram. Do you mind to explain on U.* , UP and 'from UserInfoTbl U'? Im kinda lost here.

Vikram Over a year ago

U is short name for UserInfoTbl, UP for UserPvTbl and U.* will return all the column of UserInfoTbl

Collectives™ on Stack Overflow

MySQL Query with Multiple Tables

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related