CODEWITHSUNDEEP
phpmysql
user2006120
user2006120

Reputation: 3

Select MySQL records from multiple tables and sorting

Having one heck of a time with this one.... I have the following simplified tables 

+---------+   +-------------+   +-------------+
| fields  |   | details     |   | customers   |
+---------+   +-------------+   +-------------+
| id      |   | customer_ID |   | customer_ID |
| caption |   | field_id    |   | progress    |
+---------+   | field_value |   +-------------+
              +-------------+

The fields table has multiple "fields" such as first_name, last_name etc, that have an id on them, then details would have the appropriate values in them and the Customers table handles the "master" customer information.

What I'm trying to do is get details.field_value, fields.caption WHERE details.customer_id = customers_id AND customers.progress = X and then ORDER it by the id for last_name.

I'm going crazy trying to get this to work, so thought I'd ask here and grab some lunch in the meantime.

Thank you all for the help!

Upvotes: 0

Views: 108

Answers (3)

user359040
user359040

Reputation:

One way:

select c.customer_ID,
       group_concat(concat(f.caption,':',d.field_value)) Data_Fields,
       max(case f.caption when 'last_name' then d.field_value end) last_name
from customers c
join details d on c.customer_ID = d.c.customer_ID
join fields f on d.field_id = f.id
where c.progress=?
group by c.customer_ID
order by last_name

Upvotes: 0

Simone
Simone

Reputation: 21272

Try this:

SELECT d.field_value, f.caption
FROM (details d INNER JOIN fields f ON d.field_id = f.id)
INNER JOIN customers c ON d.customer_ID = c.customer_ID
WHERE c.progress = 'X'
ORDER BY f.id

Upvotes: 0

Fabio
Fabio

Reputation: 23500

I think you can achieve this with an INNER JOIN query

SELECT c.id, c.caption
FROM customers a
INNER JOIN details b
ON a.customers_ID = b.customers_ID
INNER JOIN fields c
ON b.field_id = c.id
WHERE a.progress = ?
ORDER BY b.last_name ASC

Upvotes: 1

Related Questions