Query won't work

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Query won't work

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

SELECT a
FROM x
JOIN y ON x.a = y.a
JOIN z ON z.b = x.b
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
angus
Forum Newbie
Posts: 10
Joined: Sat Sep 17, 2005 5:43 pm

Post 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'
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
User avatar
$phpNut
Forum Commoner
Posts: 40
Joined: Tue May 09, 2006 5:13 pm

Post 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 :roll:. Your best bet is to try them out, and see the outcome.
Post Reply