Problem filtering joined recordset

[MrRiddle]

I'm trying to build a site that allows people to search for photos. There are 3 tables which are:

Names
photos
link

This allows for multiple names to be linked to a single photo.

The problem is that when I join the tables instead of getting 1 picture with, for example, 2 names I get the same picture twice with different names each time.

I have tried using the 'DISTINCT' within the SQL statement but that doesn't work. If I try and use a filter on the recordset I get the following error:

ADODB.Recordset.1 error '80004005'

SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near '; ' at line 1

If I don't join the tables and just select the information from 1 table the filter works, its only when I join the tables and put the filter on that it fails. So I assume that it has something to do with joining the tables.

Any help with be extremly helpful.

Cheers

Riddle

 

[foxbox]

Plz give us the table structure and the SQL statement with the problem (i can think of more than 1 join with these tables).



br
Gerard

 

[MrRiddle]

This is the sql statement:

sqlStr="Select * FROM link, photos, Names WHERE link.ID = photos.ID AND link.Name_ID = Names.Name_ID ;"

Table structure:
Names table
Name_ID
First_Name

link table
ID
Name_ID

photos table
ID
Place
Date
url

Is this the information that you needed?

 

[berendsen]

This will show all combinations of names and photo's:
[that is basically your SQL-statement]

Select *
FROM link l
JOIN photos p on l.id = p.id
JOIN names n on l.name_id = n.name_id


This will show all photo's for 1 name (with name_id = 1):
Select *
FROM link l
JOIN photos p on l.id = p.id
JOIN names n on l.name_id = n.name_id
WHERE l.name_id = 1

This will show all names for 1 photo (with id = 1):
Select *
FROM link l
JOIN photos p on l.id = p.id
JOIN names n on l.name_id = n.name_id
WHERE l.id = 1

 

[MrRiddle]

I've tried that and it doesn't work. I get the following error:

SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near 'ON link.ID = photos.ID JOIN Names ON link.Name_ID = Names.Name_ID' at line 1

You might notice that I have taken the aliases out to see if that made any difference but to no avail.

I'm using MySQL would that make any difference? The version is 3.23.54, which also means that I can't do subqueries.

Any other ideas?

Cheers

Riddle

 

[foxbox]

mmmmm, i use MS-SQL and have no knowledge about MySQL, but i can not imagine that such a simple SQL statement is a problem (Berendsen statements are correct).
Maybe "link" is a reserved word?




br
Gerard

 

[onpnt]

MySQL has it's subtle differences from transact. there are some conversion and comparison tables out there for how you will need to adjust the syntax of the sql statement.

on a quick google I got this hit

http://www.mssqlcity.com/Articles/Compare/sql_server_vs_mysql.htm

_____________________________________________________________________

_{Hakuna matata!!}

 

[onpnt]

from memory MySQL needs more then just JOIN for the perform.
eg:
(ref:

http://www.mysql.com/doc/en/JOIN.html

)
table_reference, table_reference
table_reference [INNER | CROSS] JOIN table_reference [join_condition]
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference [join_condition]
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference [join_condition]
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference


_____________________________________________________________________

_{Hakuna matata!!}