CODEWITHSUNDEEP
mysqlsqljoin
AVProgrammer
AVProgrammer

Reputation: 1350

SQL - Should I use a join?

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.

Upvotes: 1

Views: 4195

Answers (4)

Icarus
Icarus

Reputation: 63956

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.

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

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

Upvotes: 5

Paul Nikonowicz
Paul Nikonowicz

Reputation: 3903

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

Upvotes: 1

Mike Christensen
Mike Christensen

Reputation: 91608

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.

Upvotes: 2

Related Questions