CODEWITHSUNDEEP
sqlsqlitejoin
Brian Cooley
Brian Cooley

Reputation: 11662

SQLite query ordered by criteria in another table?

I have a SQLite database table 'products' with a list of items, each with a category and an estimated profit. Something like

|id | product  | category_id  | profit | customer_id |
|---|----------|--------------|--------|-------------|
| 1 | product1 | 1            | 15     | 0           |
| 2 | product2 | 2            | 10     | 0           |
| 3 | product3 | 1            |  9     | 0           |
| 4 | product4 | 2            | 12     | 0           |

I also have a table 'customers' that can select the products, one each. The customers have a preferred category from which they would like to select and a selection order:

|id | name        | category_id  | order |
|---|-------------|--------------|-------|
| 1 | customer1   | 2            | 1     |
| 2 | customer2   | 1            | 2     |
| 3 | customer3   | 1            | 3     |

Each product is distinct, so only one other customer may select it, which is why the customer order is important. I would like an ordered list of the products based on the customer's preferred category and order, assuming that a customer will always select the highest profit product remaining in the category. Something like:

|name        | product      | profit |
|------------|--------------|--------|
|customer1   | product4     | 12     |
|customer2   | product1     | 15     |
|customer3   | product3     |  9     |

I know I can join the tables on the category column, and I can order the list by the customer order, but I am not sure how to force the constraint that each product may be selected but once.

For example

SELECT customers.name, products.name, products.profit
  FROM customers INNER JOIN products ON customers.category_id = products.category_id
  ORDER BY customers.order, products.profit

just gives me list of all the customers crossed with all the products:

|name        | product      | profit |
|------------|--------------|--------|
|customer1   | product4     | 12     |
|customer1   | product2     | 10     |
|customer2   | product1     | 15     |
|customer2   | product3     |  9     |
|customer3   | product1     | 15     |
|customer3   | product3     |  9     |

Can you help me with the proper query to write?

UPDATE: Edited the tables above to incorporate some ideas from an answer. Assuming that products.customer_id = 0 indicates an unselected product, I can write the following query.

UPDATE products SET customer_id = customers.id 
WHERE products.customer_id = 0 AND products.category_id = customers.category_id

I don't expect this to work exactly how I want because it doesn't address the profit column yet, and I'm not sure how this query actually processes wrt customer_id. I'll test it out and answer back.

Upvotes: 2

Views: 513

Answers (1)

jefflunt
jefflunt

Reputation: 33954

Ah, I think I misunderstood the issue at first. Let me take another crack at it ...

What about adding a 'user_id' column in products that specifies which user has selected it.

If a given product can only be selected by a single user, you need some way of knowing it's already selected, and by who. I might be misunderstanding what you're doing here, but it sounds like you're trying to combine product selection (which depends on the preferred category, the maximum profit, and what's already been selected) with the additional step of querying the results. You'll likely need to break this step into two steps:

  1. In your code, figure out who gets which product based on your criteria, and mark them as such in the database.
  2. Run the query for the results.

I don't know if you can do the product selection step in a SQL query, unless you combined it with some crazy convoluted subqueries, and even then I'm not sure it's possible. If you break it into two steps your code will be simple, and so will the resulting query.

Upvotes: 1

Related Questions