0

Found a post very simular post to which im after. how to sort order of LEFT JOIN in SQL query? but mine is spread across a couple of tables.

Accounts
acc_id | salesRank | reg_date
1      | 50        | 2018-01-01 07:01:01
2      | 20        | 2019-02-01 07:01:01

Catalogue
cat_id | product_name | brand   
1      | torch        | Dewalt  
2      | MacBook      | Apple  
3      | Phone        | Samsung

Inventory
inv_id | cat_id | acc_id | salePrice | stock
1      | 1      | 1      | 999       | 6
2      | 1      | 2      | 499       | 2

Heres what I have to far:

 SELECT c.cat_id, c.product_name, c.brand, a.salePrice,
 MATCH(c.product_name) AGAINST('+dewalt' IN BOOLEAN MODE) AS relevancy, 
 (a.salesRank + i.stock) AS sellersRankTotal 
 FROM catalogue AS c
JOIN inventory AS i ON c.cat_id = i.cat_id
JOIN accounts AS a ON i.acc_id = a.acc_id

WHERE ... GROUP BY c.cat_id ORDER BY relevancy DESC

I some how need to get the MAX() sellersRank from 2 values across 2 tables combined.

The results that im after is the seller with the highest ranking (salesrank + stock) get the price shown in the search results.

 product_name | brand  | acc_id | cat_id | price | sellersRankTotal
 torch        | Dewalt | 1      | 1      | 999   | 56 (SalesRank + Stock)

I can only assume Amazon has a similar concept. Prices shown are not always the cheapest but sellers with a degree of ranking has their prices shown within its listings.

Hope this makes sense, Regards

Thamez
  • 23
  • 9
  • What's the purpose of `GROUP BY` without aggregation? – Eric May 16 '19 at 19:23
  • It groups by c.cat_id to prevent me from have duplicate products in my results. All products are grouped together, with the price of the highest ranking seller displayed. – Thamez May 16 '19 at 19:33
  • If there's no aggregation, then `DISTINCT` is what you should use. – Eric May 16 '19 at 21:07

1 Answers1

0

It sounds like you're just wanting to control the sort order differently, although I'm not 100% sure whether you meant sellersRankTotal and relevancy to be combined somehow. It doesn't matter how many source tables you have. If they're joined together in a new relation, you can sort by any column that's part of that result set (or any expression that you could use to create a calculated column).

If you just want to order by "a.salesRank + i.stock" (an expression you have given the alias of "sellersRankTotal" to), so just add that order by clause (assuming you want to sort first by relevancy and then by sellersRankTotal):

SELECT c.cat_id, c.product_name, c.brand, a.salePrice,
MATCH(c.product_name) AGAINST('+dewalt' IN BOOLEAN MODE) AS relevancy, 
(a.salesRank + i.stock) AS sellersRankTotal 
FROM catalogue AS c
JOIN inventory AS i ON c.cat_id = i.cat_id
JOIN accounts AS a ON i.acc_id = a.acc_id

WHERE ... GROUP BY c.cat_id ORDER BY relevancy DESC, sellersRankTotal DESC
Adrian
  • 91
  • 5
  • Sorry, products are displayed by relevancy DESC, while each product will have a price displayed by ORDER BY sellersRankTotal DESC. – Thamez May 16 '19 at 19:20
  • I have provided an example at the bottom of the OP. Results have acc_id as 1, price as 999 and sellersRank as 56 (50+6). Because acc_id 2 has a sellersRank of 22 (20+2) salesRank + Stock. Regards – Thamez May 16 '19 at 19:43
  • I think I understand. Is it possible for a single seller to have multiple prices for a single product? – Adrian May 17 '19 at 12:58
  • hypothetically yes... but my aim is to have multiple sellers selling the same product. each can set their own price (like ebay/amazon). Thanks – Thamez May 17 '19 at 16:49