1

I have the following sample query (MySQL):

SELECT * FROM `action` 
WHERE `customer_id` IN 
    (SELECT `id` FROM `customer` WHERE `status`=1)
ORDER BY
    action.date ASC
LIMIT 0, 10

I need to be able to ORDER BY the customer.status field. Do I accomplish this with a join?

status is a field on the customer table.

Edited Query:

SELECT * FROM `action` 
ORDER BY
    action.date ASC
LIMIT 0, 10

IMPORTANT!

I am parsing the return data via PHP. After running the revised query:

SELECT * FROM `action` a INNER JOIN `customer` c ON a.customer_id = c.id ORDER BY a.form_id ASC LIMIT 0, 10

My PHP code breaks...

This post helped me out.

My revised query looks like this:

SELECT 
    *, a.id AS lead_id, c.id AS customer_id 
FROM 
    `action` a 
INNER JOIN 
    `customer` c ON a.customer_id = c.id 
ORDER BY c.status DESC

Thanks everyone!

UPDATE

Because I have some customer records without an action record, an INNER JOIN was not returning all relevant records. I use a JOIN now, and all results come back as expected.

Community
  • 1
  • 1
AVProgrammer
  • 1,344
  • 2
  • 20
  • 40

4 Answers4

5
SELECT * 
FROM `action` a
INNER JOIN `customer` c on a.`customer_id` = c.`id`
WHERE c.`status` in (1, 4, 7, 8)
ORDER BY a.date, c.status
LIMIT 0, 10
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • both beat me to it lol. This would be the way to go AVP, although from the sound of your question you'd want to order by c.status first – Will Buck Feb 10 '12 at 19:14
  • 2
    I'm not a mySql expert, but I'm curious if there's a reason for the `c.status` in the ORDER BY clause. – Mike Christensen Feb 10 '12 at 19:16
  • And if you only want the results from the action table, you can do select a.*, and you can add a GROUP BY a.id to the end of the query – Will Buck Feb 10 '12 at 19:16
  • Excellent point Mike, your where clause defines only one status will be in the result, so there's no need to order by it – Will Buck Feb 10 '12 at 19:17
  • 1
    @WillBuck - Though I guess if you miss it, your answer gets downvoted. – Mike Christensen Feb 10 '12 at 19:20
  • The order by `c.status` is completely pointless as is, but I imagine the OP may do something like `WHERE c.status in (1,4,78)`. I have modified my answer accordingly. – D'Arcy Rittich Feb 10 '12 at 19:42
  • @MikeChristensen - Correct, that was a mistake on my part. – AVProgrammer Feb 10 '12 at 22:22
  • @RedFilter - Because I have some customer records without an action record, an INNER JOIN was not returning all relevant records. I use a JOIN now, and all results come back as expected. Just thought I'd update you! – AVProgrammer Feb 16 '12 at 18:24
2

Yes, you may accomplish it with a join and may be faster:

SELECT * FROM `action` a join customer c on c.id=a.customer_id
where c.status=1
ORDER BY
    a.date ASC
LIMIT 0, 10

Also, consider not using * and instead list the columns that you need. It will improve performance in case you need to select less than all columns and you won't get surprises in the future if the table changes.

Icarus
  • 63,293
  • 14
  • 100
  • 115
2

You can do either:

SELECT * FROM `action` a
INNER JOIN `customer` c on c.id = a.customer_id
WHERE c.status = 1
ORDER BY a.date ASC, c.status
LIMIT 0, 10

Or:

SELECT * FROM `action` a
INNER JOIN `customer` c on (c.id = a.customer_id and c.status = 1)
ORDER BY a.date ASC, c.status
LIMIT 0, 10

EDIT:

It's probably worth pointing out there's no sense in ordering by c.status, as it will always be 1. However, I put that in there since it was brought up by others as well as mentioned in the OP. I would think it could be removed from both queries.

Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
1
SELECT * FROM `action` a 
JOIN `customer` c on a.customer_id=c.id 
WHERE c.status=1 order by a.date, c.status ASC 
LIMIT 0, 10
Paul Nikonowicz
  • 3,883
  • 21
  • 39