CODEWITHSUNDEEP
mysqlsql-order-by
andrew
andrew

Reputation: 2098

mysql custom order by

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)?

Upvotes: 1

Views: 558

Answers (3)

Devart
Devart

Reputation: 122042

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;

Upvotes: 3

Gohn67
Gohn67

Reputation: 10648

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;

Upvotes: 0

Euclides Mul&#233;mbw&#232;
Euclides Mul&#233;mbw&#232;

Reputation: 1687

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

Upvotes: 0

Related Questions