...

View Full Version : help with a join query



tomharto
10-02-2011, 07:33 PM
Here's the query what's causing me some trouble.


SELECT ar.ID as aID, ar.username as auser, ar.refID, m.user_id,m.username as muser
FROM ag_refered ar
LEFT JOIN members m ON ('m.user_id' = 'ar.refID')

Ive checked the database and in ag_refered there is a record with the values
ID = 1
username = SomeUser
refID = 96

and in the members table i have a record
user_id = 96
username = testRefer

yet when i run the query in something like PHPMyAdmin i get this

aID | auser | refID | user_id | muser
1 | SomeUser | 96 | NULL | NULL



Can anyone see why?

EDIT: Both tables are in the same database and the correct database is defined in my connection. If also tried the ON ('m.user_id' = 'ar.refID') like ON ('ar.refID' = 'm.user_id') too and i get the same reuslt

Keleth
10-03-2011, 01:33 AM
Because as far as mySQL is concerned, you're joining where two (different) strings are the same. By putting it in quotes, its interpreting m.user_ID and ar.refID as strings, so...


SELECT ar.ID as aID, ar.username as auser, ar.refID, m.user_id,m.username as muser
FROM ag_refered ar
LEFT JOIN members m ON m.user_id = ar.refID



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum