Question 234695 in Mysql Question posted in
The official MySQL documentation can be found here.

Join 2 tables in MySql and sort combined results alphabetically

I am building a custom e-commerce platform for a music store using a client’s existing inventory list in which each item has a unique "System ID" (represented by a MySql table inventory).

The problem is several "unique" items in the inventory table actually need to belong to a larger "parent" item (eg. Book 1, Book 2, Book 3 in a series all belong to a single "master" or "parent" book series). To keep his catalog in tact, what I did was create a separate "Parent Items" table and then reference to the Parent Item ID whenever an item in the inventory list belongs to a larger item category. If the user clicks on an item that has a Parent ID assigned, the product info page will display the parent item and list the "children" as available options to add to their cart. Otherwise, if they click on an item that doesn’t have a Parent ID, the item just displays singly.

I need a query so that when a user clicks on a navigation Category ("Books and Music" for example), the site can do the following:

  1. Cycle through the inventory table, using an offset for pagination
  2. Join with the parent_items table
  3. Combine the sort results so they display and can loop alphabetically as a combined unit
  • For example, results display in the following order ("A book" (from inventory table), "Accent on Achievement for Trumpet"(from parent items table), "Another book" (from inventory table), "Big book series" (from parent items table),etc)

I’m able to achieve 1) and 2) easily with a join, but the sorting has me stumped. If the parent is called "Accent on Achievement for Trumpet" and the children are called "Book 1, Book 2, Book 3", I want the result to appear in "A", and not as 3 separate results. Right now it puts them in "B" because it’s cycling through the name of the inventory table and not the parent items table. It also presents the problem when there is a different parent called "Accent on Achievement for French Horn", and it’s children are also called "Book 1, Book 2, Book 3).

If I do it starting with the parent items, it will cycle through the parent_items in alphabetical order A-Z, then start over at A once it’s done with the parent_items table, which is not what I want either.

Is there a way to combine sorting of multiple tables in SQL? My experience with complex queries is limited. I’m looking for something along the lines of:

SELECT inventory.Item,
    parent_items.Item
FROM inventory
LEFT JOIN parent_items ON inventory.Parent_ID = parent_items.Parent_ID
ORDER BY (combined names???) 
OFFSET(offset depending ON page number).

Is there any way to achieve this in a single query? My pagination is dependent on the offset being correct so when they click the "Next items" or "Previous items" button, nothing gets skipped over or duplicated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    thanks for your help, after further research, I think this is what I'm looking for, at least to handle the combined sorting:

    SELECT Item as name from inventory UNION ALL SELECT Parent_Item AS name FROM parent_items ORDER BY name LIMIT 10 OFFSET 0;

    I wasn't aware of the ability to temporarily rename columns and union them together. There are so many different SQL functionalities I'm not aware of!


  2. 0

    Pagination is done via limit (LIMIT offset, row_count)

    Your ordering could be achieved in quite a number of ways, you could use a case expression for example, below I have just concatenated the 2 columns, with parent first, so that all children appear together:

    SELECT inventory.Item, parent_items.Item
FROM inventory
LEFT JOIN parent_items ON inventory.Parent_ID = parent_items.Parent_ID
ORDER BY 
   concat(parent_items.Item,inventory.Item) 
LIMIT 0,10

    But often a "front-end" will offer features (such as clicking on a column heading) that changes the sorting, so that needs consideration. Tracking the offest is also something a front-end would need to do because a user might page-up or down for example.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search