DISTINCT Query Help

[andary]

Hi guys,


I have two tables; contact_details, and gallery - which links an
advertiser to gallery photo's submitted by them.

There are many contacts, and many gallery records.

One contact can be linked up to six gallery records WHERE gallery.id =
contact_details.id

---

Here's what I'm trying solve for, let's say a small restaurant chain:

Contact Records Gallery Records
------------------------ -----------------------
Restaurant 1 (linked to) Mini Pizza
by ID Small Pizza
Medium Pizza
Large Pizza
Family Pizza
Pizza Grande

Restaurant 2 (linked to) Mini Pizza
by ID Small Pizza
Medium Pizza
Large Pizza
Family Pizza
Pizza Grande

Restaurant 3 (linked to) Mini Pizza
by ID Small Pizza
Medium Pizza
Large Pizza
Family Pizza
Pizza Grande

Restaurant 4 (linked to) Mini Pizza
by ID Small Pizza
Medium Pizza
Large Pizza
Family Pizza
Pizza Grande

Restaurant 5 (linked to) Mini Pizza
by ID Small Pizza
Medium Pizza
Large Pizza
Family Pizza
Pizza Grande

Restaurant 6 (linked to) Mini Pizza
by ID Small Pizza
Medium Pizza
Large Pizza
Family Pizza
Pizza Grande


Each restaurant is a different record no, and even though the pizza's
have similar names, they too are different record numbers.


Basically I'm wanting a query to produce DISTINCT results for
"gallery.prod_title" only; obviously there are two tables involved.


The output I'm wanting to randomly, and DISTINCT(ly) generate will be
something like this - assuming it spits out ORDER BY RAND() LIMIT 5

Pizza Grande by Restaurant 6
Small Pizza by Restaurant 5
Mini Pizza by Restaurant 6
Large Pizza by Restaurant 5
Medium Pizza by Restaurant 2


The following query is roughly what I'm working with, and I know I've
mixed in too many columns which is 'round about the point where I
through I'd post this question.


I need to print out these values (contact_details.subdomain,
gallery.prod_linkSm, contact_details.bus_name, gallery.prod_title,
gallery.prod_desc) against distinct results also....


How do I do this???
--------------------------------------


SELECT DISTINCT gallery.prod_title, contact_details.subdomain,
gallery.prod_linkSm, contact_details.bus_name, gallery.prod_title,
gallery.prod_desc


FROM contact_details, gallery


WHERE gallery.id = contact_details.id


AND contact_details.anzsic = 'XXXXXXX'
AND contact_details.list_show = 1
AND contact_details.show_gallery > 0


ORDER BY RAND()
LIMIT 5

 

[kingston via AccessMonster.com]

Do you want the selection process to be random or is just the display of
results? If the selection process is random, which data point are you
talking about (randomly selecting pizza types or randomly selecting
restaurants)? In any case, I suggest that you restructure your data into
three tables (especially since "the pizza's have similar names" - should they
have the exact same names instead?):

[Contact] - {ContactID, Details, Address...}
[Product] - {ProductID, ProdName...}
[ContProd] - {ContactID, ProductID}

Thus, each contact will be listed once and each product will be listed once.
HTH

 

[andary]

Thanks for your attention to my request.

Many restuarants have menu items which share the same name, therefore
use of two tables only.

I'm basically wanting to sort results by unique menu titles, alongside
related menu item information, such a picture of menu image, title, and
description.