Page 1 of 1
Query won't work
Posted: Tue May 09, 2006 2:36 am
by Benjamin
This is a shortened version of a very long query that isn't working. It's probably easy to fix but I can't find the problem...
Code: Select all
SELECT `blah` FROM `FriendsList` JOIN (`Profile`, `Pictures`) ON (Pictures.ID = FriendsList.IDFriend AND Profile.ID = FriendsList.IDFriend) WHERE FriendsList.ID='1'
The problem is that it's returning 0 rows, although the Friends List table does contain 2 records with the ID 1
Posted: Tue May 09, 2006 4:24 am
by timvw
Code: Select all
SELECT a
FROM x
JOIN y ON x.a = y.a
JOIN z ON z.b = x.b
Posted: Tue May 09, 2006 6:25 am
by raghavan20
look at timv's example, that is how you should be forming join. Your join has basic faulty syntax.
Each join should have an individual statement.
Posted: Tue May 09, 2006 7:34 am
by angus
Or use this:
Code: Select all
SELECT *
FROM 'FriendsList'
INNER JOIN Profile ON Profile.ID=FriendList.IDFriend
INNER JOIN Pictures ON Pictures.ID=FriendList.IDFriend
WHERE FriendList.ID='1'
Posted: Wed May 10, 2006 2:39 am
by Benjamin
raghavan20 wrote:look at timv's example, that is how you should be forming join. Your join has basic faulty syntax.
Each join should have an individual statement.
I was following the manual
http://dev.mysql.com/doc/refman/5.0/en/join.html
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
raghavan20 wrote:
SELECT a
FROM x
JOIN y ON x.a = y.a
JOIN z ON z.b = x.b
I got it to work, although I did need to change it to LEFT JOINS. I believe I had the x.a = y.a switched around.
angus wrote:
Or use this:
The friends list table doesn't have near the records that the other 2 do. This being the case should I use an INNER or LEFT JOIN?
Thanks everyone.
Posted: Wed May 10, 2006 3:24 am
by raghavan20
Sorry for the lack of my knowledge on the other possible JOIN syntax.
You can use left join, if you want to list rows, only if there are matching rows in the joining table and it is upto the developer to choose which logic he/she should follow.
for ex:
Code: Select all
select
*
from table1
left join table2 on
table2.a = table1.b
so what happens now is, each row from table1 is checked with rows of table2 and if table2.a = table1.b, the row will be listed depending on the columns specified in the select.
Posted: Wed May 10, 2006 4:46 am
by $phpNut
Code: Select all
SELECT *
FROM 'FriendsList'
INNER JOIN Profile ON Profile.ID=FriendList.IDFriend
INNER JOIN Pictures ON Pictures.ID=FriendList.IDFriend
WHERE FriendList.ID='1'
This will go and find that have equal ids in each field you listed ... i swear I'm crap at explaining SQL, anything else I can just about sort it out

. Your best bet is to try them out, and see the outcome.