CODEWITHSUNDEEP
phpmysql
Jack Hillard
Jack Hillard

Reputation: 596

MySQL - How do I order based on columns from another table

I have these tables:

Table 'Data':

| Customer | Category | Brand | Market | A | B |
|==========|==========|=======|========|===|===|
| Bill     | 5        | 4     |1       |2  |9  |
| Bill     | 5        | 4     |1       |6  |15 |
| Bill     | 5        | 4     |1       |1  |30 |
| Greg     | 7        | 9     |3       |5  |1  |
| Amy      | 9        | 2     |1       |1  |8  |

Table 'Customer':

| Cust | Mkt | SubMkt |
|======|=====|========|
| Bill | 1   | NY     |
| Bill | 2   | Arizona|
| Bill | 3   | Cali   |
| Greg | 1   | Ohio   |
| Amy  | 1   | Texas  |

Table 'Sort':

| SubMarket | SortBy |
|===========|========|
| Cali      | A      |
| Ohio      | B      |
| Arizona   | A      |
| NY        | A      |

I need to select all from 'Data' ordered by a few different columns and then either A or B depending on what 2 other tables say. 

SELECT *
FROM Data
ORDER BY Customer, Category, Brand, Market, (A or B)

Basically, Customer and Market in table Data links to Cust and Mkt in table Customer, and then SubMkt in table Customer links to SubMarket in table Sort. Then SortBy in table Sort would be the column I need to sort by in table Data. Hope that makes sense.

So, in the above example, all of the rows in Data containing both "Bill" for Customer and "1" for Market would be sorted by column A.

I started to write an array of objects in PHP and then I would sort the array based on an object property but this would actually require a huge overhaul on my existing code. I assume what I'm looking for can be done with a fairly straight forward query in MySQL but I don't know MySQL well enough to write that query. Any help would be appreciated!

Edit: I should have mentioned, these are just partial tables. The actual tables I'm working with are thousands of rows and every "SubMarket" does have a matching "SortBy".

Edit 2: Clarified that Customer and Market is needed in table Data to link to Cust and Mkt in table Customer.

Upvotes: 1

Views: 345

Answers (2)

John Ruddell
John Ruddell

Reputation: 25842

this is just an answer showing how to do the joins.. Ignacio should have the accepted answer as he provided the hard part first :)

SELECT d.*
FROM data d
JOIN customers c on c.cust = d.customer AND c.mkt = d.market
JOIN sort s on s.submarket = c.submkt
ORDER BY 
    d.Customer, 
    d.Category, 
    d.Brand,  
    d.Market, 
    CASE s.sortby
        WHEN 'A' THEN d.A
        WHEN 'B' THEN d.B
        ELSE 9999999
    END

Upvotes: 1

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798536

You need a CASE expression.

CASE `Sort`.`SortBy`
  WHEN "A" THEN `Data`.`A`
  WHEN "B" THEN `Data`.`B`
  ELSE NULL
END CASE

The ELSE NULL part is optional, but it can help reveal data problems.

Upvotes: 1

Related Questions