RichB

Quick question, its driving me mad...

Two tables.

Table1 - items

item_id
item_name
item_enabled

Table2 - pics
pic_id
pic_item_id
pic_main
pic_filename


Example data;
table 1
1,car,1
2,boat,0
3,car2,0
4,car3,1

table2
1,1,0,pic1
2,1,0,pic2
3,1,1,pic3
4,1,0,pic4
5,4,1,pic5

I want to show only the main image in a list.
Result required.
1,car,pic3
4,car3,pic5

Result I am getting so far
1,car,pic1
4,car3,pic5


Im using mysql.I want the out put to show all items whether they have an image or not in a list, against each item I want to show the image that it set to be the main image.

I want something like:
SELECT * FROM items
LEFT JOIN pics
ON items.item_id=pics.pic_item_id
WHERE items.item_enabled=1
GROUP BY pics.pic_main

This partly works as it give me the list of all items but I can't get it to only give me the ones where pic_main is set...

Can anyone shed any light on this?

Cheers,
Rich

RichB

got it now but spent all damn day on this...

Jim_B

iTrader: (1)

Wouldnt this have done what you want - if you only want results with pic_main set ?
select * from pics,items
where items.item_id = pics.pic_item_id
and pics.pic_main = 1
and items.item_enabled = 1

RichB

no because "I want the out put to show all items whether they have an image or not in a list" ... and to use the one marked as pic_main if it is set...
Sorry not too clear on my description...

I've now completely broken my installation of mySql somehow...... not a great day - LOL

GaryK

Yep an INNER join like you have in your SQL will only return rows where there is a match between the two tables and an OUTER join will return rows if there is a match or not so just add the OUTER keyword.

Gary

RichB

Actually Gary, with mysql that is an outer join.

I get all the items whether they have a main image or not, its just getting the item with the name of the main image if there was more than one.

I have done it now by getting all the main images first into a temporary table then getting all the items and doing a left join with the temp table.
the problem was i had got the SQL working using the phpmyadmin front end but that code then didnt work on my page. Weird but I've sorted it now.

Dream Weaver

Why not just loop through the items recordset and pull the images in from the while loop?

e.g

while not RSitems.EOF

'Choose pic here limiting to 1 picture

Show RSitems info here

RSitems.movenext
wend

etc etc