How to write sql to get all items in a products table & their main photo if any?

[floridaflatlander]

I've been working on this for a while and I can't figure it out.

 

I'm wanting to get all the items in the products table and if they have a main photograph in the photos table get the photo info also.

 

So when I echo the results I'll have all items without main photos displayed and all the items with main photos displayed.

 

The tables I'm using are

product

id_prod | id_mem | title | publish

 

photos

id_prod | thumb | width | height | main_photo

 

members

id_mem | mem_group      (mem_group = active or banned)

 

The sql I've been messing around with

$q = "SELECT photos.thumb, product.id_prod, product.title, photos.thumb_width, photos.thumb_height
FROM product, photos, members  
WHERE product.publish = '1'
AND product.id_mem = members.id_mem
AND mem_group >=1 AND mem_group <100
OR product.id_prod = photos.id_prod
AND photos.main_photo  =  '1'
ORDER BY product.id_prod DESC"; 

 

Any help would be greatly appreciated.

Thanks

S

[mikosiko]

and what problem do you have?....  I don't see nothing than a LEFT JOIN couldn't solve there

[floridaflatlander]

When I run it I get one picture for every item, whether the item has a main picture assigned to it or not.

 

So if I have 16 tems and 4 have a main picture I get 16 items all with the same picture.

 

As a note an item can  have no pictures, pictures but no main photo or pictures with a main photograph in the photos table.

[mikosiko]

use explicit JOIN's ... use LEFT JOIN... and is a OR that shouldn't be there... try and post the code back

[floridaflatlander]

mikosiko, I added the LEFT JOIN and took out the OR

 

		$q = "SELECT photos.thumb, product.id_prod, product.title, photos.thumb_width, photos.thumb_height
	FROM members, product LEFT JOIN photos
	WHERE product.publish = '1'
	AND product.id_mem = members.id_mem
	AND mem_group >=1 AND mem_group <100
	AND product.id_prod = photos.id_prod
	AND photos.main_photo  =  '1'
	ORDER BY product.id_prod DESC"; 

 

I kept getting this error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE sn_product.publish = '1' AND sn_product.id_mem = sn_members.id_mem A' at line 3

[mikosiko]

in general is bad idea to mix implicit (or theta) with explicit (or ansi) syntax, your query has evident syntax errors, I suggest you to read deeper in how to write and use the different kind of JOIN's existent , this could be a starting learning resource for you

http://en.wikipedia.org/wiki/Join_(SQL)  and of course the manual pages for MYSql are always available too http://dev.mysql.com/doc/refman/5.0/en/join.html

 

for now try this o see if you get what you described in your first post:

SELECT photos.thumb, 
       product.id_prod, 
       product.title, 
       photos.thumb_width, 
       photos.thumb_height
  FROM products
       JOIN members ON (product.id_mem = members.id_mem AND mem_group >=1 AND mem_group <100)
       LEFT JOIN photos ON (product.id_prod = photos.id_prod AND photos.main_photo  =  '1')
WHERE product.publish = '1'
ORDER BY product.id_prod DESC

[floridaflatlander]

Thanks, that works and looks better than this,

 

$q = "SELECT photos.thumb, product.id_prod, product.title, photos.thumb_width, photos.thumb_height
	FROM members, product LEFT JOIN photos
	ON product.id_prod = photos.id_prod
	AND photos.main_photo  =  '1'
	WHERE product.publish = '1'
	AND product.id_mem = members.id_mem
	AND members.mem_group >=1 AND members.mem_group <100
	ORDER BY product.id_prod DESC";

 

which I got to work last night, the odd thing is when I take the where clause out it doesn't work.  I'll look at the links you gave me and keep playing with these and see if I can understand it better.

 

Thanks again

S

[kickstart]

Hi

 

Mikosiko has given you a good answer, but just to follow it up and comment on the issue you had with your solution.

 

$q = "SELECT photos.thumb, product.id_prod, product.title, photos.thumb_width, photos.thumb_height

FROM members, product LEFT JOIN photos

ON product.id_prod = photos.id_prod

AND photos.main_photo  =  '1'

WHERE product.publish = '1'

AND product.id_mem = members.id_mem

AND members.mem_group >=1 AND members.mem_group <100

ORDER BY product.id_prod DESC"

 

The first bit highlighted in red is doing a join between those 2 tables. The columns it does the join on are highlighted in the 2nd bit in red. So if you remove the WHERE clause you remove the part that tells it which columns to join on.

 

This should still work, but what it will do is join every row from those 2 tables (a CROSS JOIN), irrespective of the id_mem fields. This is one way to get a massive number of records returned (if each table had 100 records, you would get 10000 rows brought back).

 

Cross joins are useful. For example if what you wanted to produce was a grid of all the members and all the products, highlighting how many each member has bought of each product you could use a CROSS JOIN between the members and products table, with a LEFT OUTER JOIN to get the matching data (if it exists).

 

All the best

 

Keith

[floridaflatlander]

Thanks kickstart &  mikosiko.

 

This forum is the best.

 

And this explains why I was getting 90 plus results from 15 records with some of the code I played with last night

This should still work, but what it will do is join every row from those 2 tables (a CROSS JOIN), irrespective of the id_mem fields. This is one way to get a massive number of records returned (if each table had 100 records, you would get 10000 rows brought back).

 

Thanks again

S