Selecting rows from multiple tables

[rayman0487]

Hey All! I'm writing a PHP app that will be deployed to a LAMP on AWS. What I'm currently trying to do is get some customer data and it requires 3 tables to get all the relevant information. First my table structure (it is slightly simplified)

+------------------+     +----------------------+          +-------------------+
| settings_fields  |     | customer_info        |          | customer_details  |
+------------------+     +----------------------+          +-------------------+
| field_id         |     | customer_id          |          | customer_id       |
| field_caption    |     | field_id             |          | progress          |
+------------------+     | field_value          |          +-------------------+
                         +----------------------+

I have this setup to allow my users to specify custom fields that they will want to save to the DB when adding a new customer, but there are several fields that I have set as a default that have field_id already set. When a customer is added a customer_id is generated and stored in customer_details and then all fields are saved as individual rows in customer_info. Customer_info field_id matches settings_fields.field_id. So, for example, settings_fields.field_id (2) is field_caption (Last Name)

 

What I'm trying to do is this.

1. get all customer_details.customer_id where progress=1

2. get those customers' customer_info.field_value, settings_fields.field_caption WHERE field_id matches

3. Get those as individual rows for each customer.

4. Sort the list by last name of customer so ORDER BY settings_field.field_id = 7

 

Is this possible? My head is spinning and I've been trying to work on this for a while now.

 

What I'm currently doing is getting all the customers where progress = 1 then foreach looping through and getting the details, but I can not figure out how to then sort it...

 

Any advice?

[computermax2328]

Where are you at with this? Did you try using a MYSQL JOIN? Post what you have just for the query.

[Psycho]

Most of what you want is pretty basic and can be achieved with some basic JOINs

 

SELECT cd.customer_id, sf.field_caption, ci.field_value

FROM customer_details AS cd
JOIN customer_info AS ci
  ON cd.customer_id = ci.customer_id
JOIN settings_fields AS sf
  ON ci.field_id = sf.field_id

WHERE cd.progress = 1

 

The trick is the last requirement to sort the results by the last name. Each row in your result set will be a different field from the customer data and only one of those is the last name. So, you need to "add" the last name to all the other fields for the purpose of sorting. You could do this in the query or you could do it while processing the result set. But a query would probably be the cleanest approach. (Although I'm not sure why you didn't include the default fields as part of the customer_details table and only used the other two tables for custom fields, would make more sense to me).

 

Anyway, this should work, but no guarantee since I didn't feel like creating a database to test it.

 

SELECT cd.customer_id, sf.field_caption, ci.field_value,
       last_name.field_value as last_sort

FROM customer_details AS cd
JOIN customer_info AS ci
  ON cd.customer_id = ci.customer_id
JOIN settings_fields AS sf
  ON ci.field_id = sf.field_id
JOIN customer_info as last_name
  ON cd.customer_id = last_name.customer_id
  AND last_name.field_id = 7
WHERE cd.progress = 1

ORDER BY last_sort

[rayman0487]

Sorry, had a crazy week and then was out of town for the weekend.

 

 (Although I'm not sure why you didn't include the default fields as part of the customer_details table and only used the other two tables for custom fields, would make more sense to me).

 

This is what I think I'm going to do. I was thinking about this over the weekend and it hit me today. I thought, "ya know, all customers will have this field, I mind as well "hard code" it into the DB." I think this is the best and cleanest solution.

 

Thanks for taking the time to give the sql examples though. I appreciate the help.