Complicated mySQL statement only partially works

Databases
1

Hi all, I have the mysql statement in my php page. I’ve also tried running it through Navicat and phpMySQL and get the same (wrong) results.

I want to show all records but only get those that have sold.

The mySQL statement is as follows:

SELECT prints.print_key, 
	negatives.print_title, 
	negatives.image, 
	prints.print_id, 
	prints.paper_type, 
	prints.status, 
	prints.date_printed, 
	prints.date_of_disposition, 
	prints.frame_type, 
	prints.frame_width, 
	prints.frame_height, 
	prints.glass_type, 
	prints.frame_size_units, 
	prints.studio_location, 
	prints.contact_id, 
	prints.sale_price, 
	prints.currency, 
	editions.list_price, 
	editions.currency, 
	editions.width, 
	editions.height, 
	editions.image_size_units, 
	editions.edition_id, 
	editions.edition_size,
contacts.first_name,
contacts.last_name,
	CONCAT_WS(' ',contacts.first_name,contacts.last_name) AS fullName, 
	CONCAT_WS(' / ',prints.print_id,editions.edition_size) AS printNumber, 
	CONCAT_WS(' x ', editions.width, editions.height) AS printSize, 
	CONCAT_WS(' x ', prints.frame_width, prints.frame_height) AS frameSize, 
	contacts.full_name
FROM collections INNER JOIN negatives ON collections.collection_id = negatives.collection_id
	 INNER JOIN editions ON negatives.negative_id = editions.negative_id
	 INNER JOIN prints ON editions.edition_id = prints.edition_id
	 INNER JOIN contacts ON contacts.contact_id = prints.contact_id;

Any help and/or advice would be most appreciated.

2

Happy you found it. Those Joins can be a pain :slight_smile:

3

Is there a seperate field for the sold items

4

OK, I think I found it…

This line:

 INNER JOIN contacts ON contacts.contact_id = prints.contact_id;

should be:

LEFT OUTER JOIN contacts ON contacts.contact_id = prints.contact_id

Will keep testing though…

5

Hi Don, thanks for replying. No, there is a field called status, which gives the state of the piece (printed, in a studio, on loan, sold…).

But I’m not testing for it…

David