mysql custom order by

Question

I have two tables. One has some product details, the other holds various photos per product. For my problem only the following fields are used:

tableA id - prodName tableB id - prod_id - photoName

where tableA.id = tableB.prod_id

Not all products have photos (in other words not every tableA.id exists in tableB). I am only interested in the first photo of each product (that has photos).

What query would allow me to list all products on my page, showing first the ones that have photos and at the end the ones that don't? Both sets must be alphabetical ordered based on product name.

So far i have tested the following without 100% success

Using two queries: Query 1st: SELECT prod_id FROM tableB WHERE photoName <> '' GROUP BY prod_id ORDER BY photo_name this will give me (with some php scripting) a list (comma seperated) of all products with photos, sorted by photo name Query 2: SELECT ..... FROM tableA WHERE ..... ORDER BY FIND_IN_SET(id, '$myList') DESC, prodName ASC

The second query will filter the products that have/have not photos, but not sorted by product name (because the original list is ordered by photo name).

Is there a way to accomplish what i want in a single query (if possible)?

Devart · Accepted Answer · 2012-03-27 07:23:42Z

Try this query -

SELECT a.prodName FROM tableA a LEFT JOIN tableB b ON a.id = b.prod_id GROUP BY a.id ORDER BY IF(b.prod_id IS NULL, 1, 0), a.prodName;

Euclides Mulémbwè · Accepted Answer · 2012-03-27 07:09:14Z

try left join since not all products have photos:

SELECT * FROM tableA tA LEFT JOIN tableB tB ON ta.id = tB.prod_id WHERE tB.id = (SELECT MIN(id) FROM tableB tB1 WHERE tB1.prod_id = tB.prod_id) ORDER BY prodName, photoName

if you only whant the products with photo you can use INNER JOIN instead:

SELECT * FROM tableA tA INNER JOIN tableB tB ON ta.id = tB.prod_id WHERE tB.id = (SELECT MIN(id) FROM tableB tB1 WHERE tB1.prod_id = tB.prod_id) ORDER BY prodName, photoName

Gohn67 · Accepted Answer · 2012-03-27 07:15:04Z

You can union the results.

select tableA.prod_id, tableA.prodName, tableB.photoName from tableA inner join tableB on tableA.id = tableB.prod_id group by tableA.id order by tableA.prodName asc union select tableA.prod_id, tableA.prodName, tableB.photoName from tableA left join tableB on tableA.id = tableB.prod_id where tableB.photoName is null order by tableA.prodName asc;

Collectives™ on Stack Overflow

mysql custom order by

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related