query from two tables?

one query to choose information from one out of two tables

[Warboss Alex]

Hey everyone,

My problem is this. I'm building php forums, and, for site-specific reasons, users on the forums can have one 'main' name, their username which they register with, and also as many 'extra' names as they want.

For use on the forums, both the username and any extra names can have customised icons and signatures for that name.

Like, a user named 'Alex' might have Alex.jpg as his icon, and some text as a signature. Alex, might also have 'AlexTheGreat' as an extra name, with AlexG.gif as his icon, and some text2 as a signature.

On the posting screen, a user can choose which name to post under from a drop-down list. The script which handles the post must be able to get the userID from the users table (in which usernames are stored), and either the name, icon and signature from the users table, OR the name icon and signature from the extra names table.

Is there any way to do this with just one query? My old script checked if the name entered was a username or an extra name, and then took the information from whichever table was appropriate, but that took three queries.

Is there a way to do it with one? Any help would be most appreciated.

The extra names are linked to their owner username by the userID field, by the way ..

Thanks in advance!
Alex ...

[lorax]

Yup. What you want is called a JOIN [dev.mysql.com].

[Warboss Alex]

What d'you mean?

SELECT u.userID, u.username, u.icon, u.sig, e.extraname, e.icon, e.sig FROM users u, extras e WHERE ((u.username = $post_name AND u.userID = e.userID) OR (e.name = $post_name AND u.userID = e.userID));

How would that query know which 'icon' to use, for example, or sig? And I don't want to return unnecessary fields, just those the query needs, i.e. one name, one userID, one icon, one sig.

If I can't get round that, what happens to fields with the same name? Like u.icon and e.icon?

[lorax]

Sticky me the table structures.

[Warboss Alex]

Thinking again on it, you don't need a join between the tables. All you need is for the query to return the fields id, name, icon and sig from whichever table it finds the $name variable in.

Would this work?

SELECT icon, sig, id FROM users u, extras e WHERE (e.extraname = $posted_name OR u.username = $posted_name);

I can't check it right now. Would that select fields from both tables or just one, the one where it finds $posted_name?

Table structures:

users:
------
username (varchar)
id (medium int)
icon (varchar - path to file)
sig (text)
//plus other irrelevant fields..

extras:
-------
extraname (varchar)
id (medium int - id from users table)
icon (varchar)
sig (text)
eID (primary key)

[lorax]

>> from whichever table it finds the $name

That would be correct providing there are no duplicate names. The addition of the ID would be better here.

SELECT id,icon,sig
FROM users,extras
WHERE ((extras.extraname = $posted_name OR users.username = $posted_name) AND (users.id = $posted_id OR extras.id = $posted_id))

It think that's the correct though I'm not sure you can call the field names without naming the tables they come from. It might have to be:

user.id,extras.id,users.icon,extras.icon,users.sig,extras.sig

which might cause problems because one of the tables won't satisfy the WHERE clause.

You might be able to use a subquery [dev.mysql.com] in the FROM clause to determine which table to use but I'm not sure of what the syntax would be or if it's even possible.

[Warboss Alex]

There are no duplicate names; what's the point of exclusively owning an extra name which someone else already has? ;)

I don't think my server version supports subqueries anyway .. (it's 4.0.18 if I remember rightly) ..

But even so, I can't see how it'd work. The subquery doesn't return a reference to a table .. ooh, now that'd be good, having a SELECT query return a reference to a table. It could SELECT fields based on unser input and return the table name or whatever, to use in the FROM clause to get just the info from the right table.

I don't suppose that's already possible?

[lorax]

>> a SELECT query return a reference to a table

That's where I was headed but like I said, I don't know if it's possible. The only other way I could see to do this is to use the modifier 'count' in a seperate query combined with an if/else statement but then we're adding on a bunch of code for what should be a simple exercise.

[Warboss Alex]

Well, there's plenty of food for thought here, but if we can't find what we're looking for, or it's just not possible with the technology available, we'll just have to resign ourselves to the next best thing ..

A straight join on the two tables, retrieving, say icon1 and icon2 fields, from each table, then a little conditional to check which one we want.

I can't see any other way to do it, at the moment, can you?

Thanks for all your help!

[coopster]

A straight join on the two tables, retrieving, say icon1 and icon2 fields, from each table, then a little conditional to check which one we want.

I would do it that way. Either that or a query against one table, check for any returned rows. If none found, run the query on the next table.

The only other way I can think of off-hand would be a control flow function, but as lorax stated, more effort than it's worth. Example:

SELECT 
 IF(users.username='$post_name',users.id,extras.id) AS myid, 
 IF(users.username='$post_name',users.username,extras.extraname) AS myname, 
 IF(users.username='$post_name',users.icon,extras.icon) AS myicon, 
 IF(users.username='$post_name',users.sig,extras.sig) AS mysig 
 FROM users, extras 
 WHERE users.id=extras.id AND users.username='$post_name' 
 OR users.id=extras.id AND extras.extraname='$post_name' 
 LIMIT 1 
;