MySQL Join

[jason32]

I have a bit of an understanding of how a join works in a SQL query, but it's really basic and I can't quite get my head around it enough to figure out how to do what I need.

Here's the situation. I have two tables, one named pages which has columns (amongst others) called create_id and modified_id and a second table named uses which has id as a key and a column (amongst others) called realname.

What I want to do is SELECT * from pages, but in addition to create_id and modified_id I also want to look these id's up in the users table and return the realname that corresponds to each of these id's as well.

I hope my question makes sense. Thanks in advance for your help, and let me know if I'm not being clear enough :happysad:
Edit:
I should probably add that I'm hoping to do this with just one SQL query if I can.

 

[Twinkie]

Yes this is possible, and thanks for being so clear. Most people don't understand how to ask for help

This a query that should work for you:

SELECT * FROM pages
INNER JOIN uses ON id = create_id OR id = modified_id;

Tell me if this works for you. If you have any preference in how the results should be sorted, then post back.

Currently, this will return everything from both tables when there is a match between either table IDs. You will probably want to be a bit more concise.

 

[descalzo]

Think you have to use a three table join, pages with uses with uses

SELECT [fill in pages cols] , usersOne.realname, usersTwo.realname
FROM pages, usersOne uses , usersTwo uses
WHERE create_id = usersOne.id AND modified_id = usersTwo.id

Note: you said the second table was "uses" and that is what I used. Did you mean "users"?

Also assumed create_id and modified_id are not Null

 

[jason32]

Thanks so much for your help!

Twinkie: the query works great, but assuming I'm looking for just one record from pages (I almost always am in this application) it returns either one or two rows, depending on whether the user that created the page is also the user that last modified it. This is fine and it's certainly a better solution that what I'm doing right now (three MySQL queries in the same script) but would the two rows returned always be in a particular order (id = create_id first, then id = modified_id next) or will I have to return the id column also and test for which row is which in the PHP script?

descalzo: This seems like it would be the perfect solution for me - one row returned with all the information I need in it - but when I try running the query I get a SQL error back saying "Not unique table/alias: 'users'

You're assumptions are good ones though. create_id and modified_id are never null, and the id contained within them always exists in the users table. Also I did mean 'users' rather than 'uses.' Typo :happysad:

Thanks again for your help guys!

 

[descalzo]

SELECT * 
FROM `pages` AS p
JOIN `users` AS u
JOIN `users` AS uu
WHERE p.id_create = u.id
AND p.id_modify = uu.id

I got this to work from phpAdmin on a test mySql db.

 

[jason32]

Thanks descalzo, that's awesome. It does exactly what I was trying to achieve! :biggrin:

 

[Twinkie]

The ON clause is supposed to replace the WHERE clause in JOIN statements?

The correct way to do this can be achieved with only one join, but it doesn't matter if it works.