SQL Should I use a connection?

Question

I have the following query example (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 on the field customer.status. Am I doing this with a join?

status - field in the customer table.

---

Edited query:

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

---

IMPORTANT!

I am analyzing the returned data through PHP. After completing the revised request:

 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 is breaking ...

---

This post has helped me.

My revised request is as follows:

 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

Since I have customer records without an activity record, INNER JOIN did not return all the relevant records. Now I am using JOIN and all results are returned as expected.

Answer 1

 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 

Answer 2

Yes, you can execute it with a connection and can 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, do not use * and instead list the columns you need. This will improve performance if you need to select fewer than all columns and you will not get surprises in the future if the table changes.

Answer 3

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 may be worth noting that it makes no sense to order on c.status , since it will always be 1 . However, I said that there, since he was brought up by others, and was also mentioned in the OP. I think it can be removed from both queries.

Answer 4

 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