MYSQL Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mojeIme
Forum Newbie
Posts: 22
Joined: Sat Jul 29, 2006 8:58 am

MYSQL Query

Post by mojeIme »

Hi, I am new with mysql, having problem with "transfering" ids in first table with real names from another table connected by id. In this code i menaged to do it with t1.host_id into t2.username, but i dont have idea how to do it with t1.b_seat and t1.b_seat. They are integers in table "tabla".

Code: Select all

$query = "SELECT t1.room_id as Room, t2.username as Host, t1.w_seat as White, t1.b_seat as Black, t1.vrijeme as Time, t1.povecanje as Increment FROM tabla as t1, player as t2 WHERE t1.host_id = t2.player_id";
Could it be done like :

Code: Select all

$query = "SELECT t1.room_id as Room, t2.username as Host, t2.username as White, t2.b_seat as Black, t1.vrijeme as Time, t1.povecanje as Increment FROM tabla as t1, player as t2 WHERE t1.host_id = t2.player_id";
... but then i am repeating t2.username. Please help.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You are doing a simple join. I really cannot make my way through your queries, but if you could post your table structure for both the tables and tell me which fields are related and what you want selected, I (or any of the other SQL nerds around here) can put together a query for you.
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post by wtf »

mojeIme..... Odakle si?
User avatar
mojeIme
Forum Newbie
Posts: 22
Joined: Sat Jul 29, 2006 8:58 am

Post by mojeIme »

Table player :

-player_id
-username
-password
-status
-email

Table "Tabla":

- tabla_id
- host
- bijeli
- crni
- vrijeme
- povecanje

In the table "Tabla", primary key is tabla_id, and host,bijeli and crni are related with player_id from table "Player". I need to get 3 usernames from
"Player", having their ids as host,bijeli and crni .Thanks for the reply.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

OK, that is not a simple join. I think you might want to look into union queries. You can do a join, but it might be more overhead than it is worth.

You have three fields in the 'Tabla' table that correspond to the player_id field in the 'Player' table?
User avatar
mojeIme
Forum Newbie
Posts: 22
Joined: Sat Jul 29, 2006 8:58 am

Post by mojeIme »

Correct, any goot tutorial on union queries?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Post Reply