How to join 2 queries from 2 different tables

Question

EDIT: The key between these 2 table is the id # of the guest.

I want to run 2 queries from 2 different tables at the same time :

I want to create a list that a host could chek out to see every person their have ever invited. I want to show the username + location of the guestinvited

 $table = query("SELECT guest FROM parties WHERE host = $uid");   

 $table2 = query("SELECT id, username, location FROM users WHERE id = $uid");

$uid is the current user checking out the history of the guests their invited.

parties table

event | guest | host

xmas | 12 | 14

users table

id | username | location

12 | caroline | NYC

How should I do that ?

Please double-check your table and column names. Is it a parties table? or members? Is `$uid` a number (12), or a name ('marc')? Did you confuse the guest and host columns in your first query? — jcsanyi, Jul 05 '13 at 23:39
possible duplicate of [sql join two table](http://stackoverflow.com/questions/9171963/sql-join-two-table) — Fallen, Jul 05 '13 at 23:40
@jcsanyi $uid is a number (12). I just edited the name of the table. thx — Caro_deb, Jul 05 '13 at 23:41
You should rethink your data structure a little. It appears to me that only way to achieve what are asking is to join user.username to parties.host (as in the answer by @Daniel) Treating a person's name as a key in this way is a very bad practice. What if two people have the same name? They will see each other's data! What if someone wants to change their name in the system? They will lose all their old data. Consider a unique number for the key instead. — AllInOne, Jul 06 '13 at 00:17
The key is a unique id : see above, i edited my question. the id 12 is the link between the 2 tables. — Caro_deb, Jul 06 '13 at 00:27

Daniel Kmak · Accepted Answer · 2013-07-06T00:33:06.723

2

$query = query('SELECT `u`.`username`, `u`.`location` FROM `users` u JOIN `parties` p ON `p`.`guest` = `u`.`id` WHERE `p`.`host` = ' . $uid)

edited Jul 06 '13 at 00:33

answered Jul 05 '13 at 23:40

Daniel Kmak

18,164
7
66
89

should i replace all the Ps with parties and the Us with users ? – Caro_deb Jul 05 '13 at 23:50
You could - it's your choose but it doesn't matter at all. – Daniel Kmak Jul 05 '13 at 23:54
could you please review my question again ? I edited it. The unique key between the two table is the id (12) . – Caro_deb Jul 06 '13 at 00:30

score 0 · Answer 2 · edited Jul 06 '13 at 07:03

0

It doesn't matter if you run 2 query at the same time. But you can use this if you want getting result in one variable

$query = SELECT id, username, location FROM users
LEFT JOIN parties 
ON users.host = parties.host

edited Jul 06 '13 at 07:03

NullPoiиteя

56,591
22
125
143

answered Jul 05 '13 at 23:57

Angripa

159
2
4
14

score 0 · Answer 3 · answered Jul 06 '13 at 00:00

Basically, all you need to do is to use the JOIN clause. I hope the below code helps out: $sql = ("SELECT users.id, users.username, users.location, parties.guest JOIN USING (id) WHERE id = '$uid'"); $query = mysql_query($sql) or die('Failed to perform query: ' . mysql_error);

How to join 2 queries from 2 different tables

3 Answers3