SQL question: join

trexx

using mysql, i've got two tables:
- user
- user_rel

user
---------------------------
user_id int
user_name varchar(30)

user_rel
---------------------------
user_rel_id int
owner_user_id int
author_user_id int

now i'd like to select a single row from user_rel that returns user_rel_id, owner_user_name and author_user_name. can i do that with a single query in mySql?

guess it should be done with some sort of a join... thanx for your help

getDizzle

Well, the data you presented does not correspond to the fields you want to pull. Also, you don't tell us if there's an ID to relate both tables. So, generally speaking, to join to mysql tables you should have an id in table2 that relates to data in table1. Then you could use a simple straight join:

Quick and dirty example:

[users] - t1
id
username
password

[profiles] - t2
id
users_id
name
address
bio

Then your query to join these two tables would be something like:

SELECT t1., t2. FROM users as t1, profiles as t2 WHERE t1.id = t2.users_id

http://www.mysql.com has much documentation on joins under SELECT syntax if my instructions aren't clear enough. Good luck.

peppeto

First make one more colom in user table something like user_rel_FK

user

user_id int
user_name varchar(30)
user_rel_FK int (10)//or whatever tinyint

SELECT * FROM user AS u, user_rel AS ur WHERE (user_rel_FK = ur.user_rel_id)
Sorry for my english
peppe

trexx

sorry... looks like i was a bit imprecisely.

the table user_rel is just an example... it's not a real-life table. but i use it to demonstrate my problem: the table has two foreign-keys, linked with the user table. (@ peppeto: i do not wanna have a 1:1 relationship between a user and my fictional user_rel table, the relationship between user and user_rel is 1:n)...

what i wanna have is something like this: (in not mysql compliant sql g)

SELECT
	user_rel_id AS user_rel_id,
	(SELECT user_name FROM user WHERE id=owner_user_id) AS owner_name,
	(SELECT user_name FROM user WHERE id=author_user_id) AS author_name
FROM
	user_rel r,
	user u
WHERE
  user_rel_id = 1

stolzyboy

if you get a newer version of mysql, it supports subselects

trexx

ok - guess i have to try the new version in a few years (maybe my provider has managed to install it until then g). i wasn't sure if there is a way to do something like that without subselects or ugly php scripting. but i think for the moment the latter will be the solution...

thanks for your help.

matt_4013

trexx, can you post your actual table structure? Just the relevant fields is fine. Basically, just want your data tables and transaction tables, with all keys, and the fields you want selected. I'm ALMOST sure I know what you want, and it's just a matter of a couple of joins. Give us the structure and I'll write it up for you 🙂

stolzyboy

i am sure there is a way to do it with either joins or some arrangement of multiple queries, which essentially is what subselects are, they just do it in one fell swoop, not multiple queries

trexx

@: It was just an fictional table, i was interested in the solution. But i changed my example, so that it is a bit easier to understand what i'd like to do. There is a table for different tennis-courts, each court has two players.
Now, let's say that i wanna have the names of both players on court 2 (that would be Garfield & tREXX)...

I'm really curious how you do that with one query...

PLAYER (structure)
----------------------------------------------
INT 			PLAYER_ID
VARCHAR(30)		PLAYER_NAME



TENNIS_COURT (structure)
----------------------------------------------
INT 			COURT_ID
INT				FK_PLAYER1_ID
INT				FK_PLAYER2_ID



PLAYER (data)
PLAYER_ID		PLAYER_NAME
----------------------------------------------
1				tREXX
2				Tolkien
3				Garfield
4				Dumbo



TENNIS_COURT (data)
COURT_ID	FK_PLAYER1_ID	FK_PLAYER2_ID
----------------------------------------------
1			1				2
2			3				1
3			3				2
4			4				2

matt_4013

Ahhh, I see. You've got your database structure set up a bit weird. What you really want here is to change your court table around a bit.

TENNIS_COURT (structure)

id, unsigned int, primary key, auto_increment
court_id unsigned int
player_id unsigned int

TENNIS_COURT (data)

id, court_id, player_id

1, 1, 1
2, 1, 2
3, 2, 3
4, 2, 1

Then you use a simple left join query:

select P.PLAYER_NAME from PLAYER as P left join TENNIS_COURT as TC on P.PLAYER_ID=TC.PLAYER_ID where TC.COURT_ID='2'

This will return 2 rows, (id's 3 and 4) that would contain the names of the players on the court.

ahundiak

Here is an example from the sql manual
http://www.mysql.com/doc/en/JOIN.html
SELECT names.codename,
s1.score AS "Score1", s1.comment AS "Comments1",
s2.score AS "Score2", s2.comment AS "Comments2",
SUM(st.score) AS "Total"
FROM students names
LEFT JOIN scores s1 ON s1.act_id=1 AND names.id=s1.student_id
LEFT JOIN scores s2 ON s2.act_id=2 AND names.id=s2.student_id
LEFT JOIN scores st ON names.id=st.student_id
WHERE names.codename <> ''
GROUP BY names.codename
ORDER BY names.codename;

Basically you do a left join for each player, give it a name and then use the join name to pull data on each player.

However, matt_4013 is correct in that you really should not have two player fields in the court table. Consider trying to determine what courts a given player has reserved. You will be constantly doing OR's in your query. Plus, what happens if 4 people want to play doubles?

Use three tables
PLAYER - player_id name
COURT- court_id name
PLAYER_COURT player_id court_id

trexx

thanks a lot to all of you. it was really just a theoretical question, you know... so don't bother with my odd tables =)

the left join thing looks interesting... guess it gets a bit slow if you do that on large tables... but after all i searched for a one-query solution g

matt_4013

A left join would be far faster than multiple queries 🙂

trexx

sure? well i guess that depends on the situation ... i tried several queries/code combinations to optimize the speed in the forum on my homepage... and it showed that one single join was much slower than the one with three queries...

matt_4013

No offence, but if that's the case, then you've got your database set up wrong.

trexx

hm... good point... maybe i should really take a look at those "optimize your db" topics =)

Anyway... thanks for your help.