[RESOLVED] join problem

adeibiza

table1 consists of

id | cat | name
1 | 1 | notinterested
2 | 2 | name a
3 | 2 | name b
4 | 2 | name c
5 | 3 | notinterested

table2

id | catid | url
1 | 2 | a.jpg (name a)
2 | 2 | b.jpg (name a)
3 | 2 | c.jpg (name a)
4 | 3 | d.jpg (name b)
5 | 3 | e.jpg (name b)
6 | 3 | f.jpg (name b)
7 | 4 | g.jpg (name c)
8 | 4 | h.jpg (name c)
9 | 4 | i.jpg (name c)

the catid in table2 is based on the original id from the table1

so basically what i need to do is select everything from table one that has cat=2 and display that name next to its 1st corresponding url from table 2

so the echo result would be

name a - a.jpg
name b - d.jpg
name c - g.jpg

this is my current select which fails but i cant see why

SELECT table1.cat, table1.id, table2.id, table2.url, table2.catid FROM table1, table2 LEFT JOIN ON table2.catid = table1.id WHERE table1.cat = '2' GROUP BY table2.catid

adeibiza

SELECT table1.cat, table1.id, table2.id, table2.url, table2.catid FROM table1 LEFT OUTER JOIN table2 ON table2.catid = table1.id WHERE table1.cat = '2' GROUP BY table2.catid

johanafm

When you get error messsages, please include them in your post since it makes things easier for those reading it. The join is not written correctly. The syntax is JOINTYPE JOIN tablename ON ..., which means

FROM table1
LEFT JOIN table2 ON table2.catid = table1.id

But you probably will not get the results you are looking for. If you are using a database sticking to SQL standards, then your query still will not execute. If you are using MySQL, it will - but the results aren't predictable.
When having a GROUP BY clause, every field must be either part of the GROUP BY clause or part of an aggregate function, such as sum, count etc, for very good reasons.
Consider some data in table t

name		contest    season   points
jane		1		1		5
jane		2		1		2
jack		1		1		3

Now, let's say you issue this query in MySQL

SELECT name, season, SUM(points) FROM t
GROUP BY season

All or most other DBMSs would reject the query. Mysql will display

XXX	1	10

Where XXX is either jane or jack. You simply do not know which will be selected, and the output will suggest that either jane or jack had 10 points for season 1, which is not correct. To make the query show useful data (and adhere to SQL standards), you should either include name in the group by clause, or omit name from the selected fields, or use an aggregate funcion such as group_concat on name.

To retrieve only one of possibly many rows from table2, you can't just join table1 and table2 together, since that will include more than one row for each row from table1. You will need to join on a nested query. Also note that your use of the word "first" has no meaning unless you include an order by clause, since a table is not inherently ordered in any particular fashion.
Breaking things down into pieces, first deal with getting the "first" row

-- This selects the "first" (assuming lowest) t2.id for each t2.catid which
-- corresponds to t1.cat = 2
SELECT MIN(id), catid
FROM table2 t2
	INNER JOIN table1 t1 ON t1.id = t2.catid
WHERE t1.cat = 2
GROUP BY catid

which can then be joined to the actual tables

SELECT name, url
FROM 
	(SELECT MIN(id) AS id, catid
	FROM table2 t2
		INNER JOIN table1 t1 ON t1.id = t2.catid
	WHERE t1.cat = 2
	GROUP BY catid) AS t
INNER JOIN table1 ON table1.id = t.catid
INNER JOIN table2 ON t.id = table2.id