CODEWITHSUNDEEP
phpmysqlinner-join
steve
steve

Reputation: 23

mysql weird multiple inner join situation

my situation:

Table "_customers"

----------------------------------------------
customer_id    |    name    |    active
----------------------------------------------
     1          'customer I'       1
----------------------------------------------
     2          'customer II'      0
----------------------------------------------

Table "_projects"

----------------------------------------------
project_id    |    project_name    | customer_id
----------------------------------------------
     1             'project I'           1
---------------------------------------------- 
     2             'project II'          2
----------------------------------------------

many-to-many Table "_project_sections"

----------------------------------------------
section_id  |  project_id
----------------------------------------------
     2             1
----------------------------------------------
     3             1
----------------------------------------------
     4             1
----------------------------------------------
     1             2
----------------------------------------------

In my Case, i need now all Customers, which are 'active'. Also they must be from a specific Section. So, as a example, i want all active customers from the Section "1". I also figured it out to get the right data, but - and thats the weird thing - only if need sections with the id > 1. If i try to get all active customers in section 1, the Query still show me all Projects with Section_id 2,3,4...

Query

SELECT c.customer_id, c.name, ps.section_id
FROM _customers c
  INNER JOIN _projects p
     ON p.customer_id = c.customer_id 
  INNER JOIN _project_sections ps
    ON ps.project_id = p.project_id 
  WHERE c.active = 1 AND ps.section_id = 1
GROUP BY c.name
ORDER BY LOWER(c.name)

And the wrong result:

Array
(
[customer_id] => 1
[name] => customer I
[section_id] => 1
)

Maybe someone can help me, because i dont't get it. Thank you so much!

Upvotes: 2

Views: 147

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

You don't need a GROUP BY clause to write this query.

First, let's look at active customers and their projects.

-- All active customers 
SELECT c.customer_id, c.name
FROM customers c
WHERE c.active = 1;

customer_id    name
--
1              customer I

-- All active customers and their projects
SELECT c.customer_id, c.name, p.project_id, p.project_name
FROM customers c
INNER JOIN projects p ON (p.customer_id = c.customer_id)
WHERE c.active = 1;

customer_id  name          project_id  project_name  
--
1            customer I    1           project I
1            customer I    2           project II

Just one active customer, who has two projects.

Now let's look at section 1 projects.

-- All projects from section 1
SELECT ps.project_id, ps.section_id
FROM project_sections ps
WHERE ps.section_id = 1;

project_id    section_id
--
2             1

-- All projects from section 1, including project name
SELECT ps.project_id, p.project_name, ps.section_id
FROM project_sections ps
INNER JOIN projects p ON (p.project_id = ps.project_id)
WHERE ps.section_id = 1;

project_id    project_name    section_id
--
2             project II      1

Just one section 1 project. Now put the pieces together.

-- All active customers and their projects from section 1
SELECT c.customer_id, c.name, p.project_id, p.project_name, ps.section_id
FROM customers c
INNER JOIN projects p ON (p.customer_id = c.customer_id)
INNER JOIN project_sections ps ON (p.project_id = ps.project_id)
WHERE c.active = 1 AND ps.section_id = 1;

customer_id  name          project_id    project_name    section_id
-- 
1            customer I    2             project II      1

That's what I'd expect.

Later . . .

I see that you've changed the data. If I make the same changes here, then my last query above returns no rows. I think that's what you said you expected. (And, with your changes to the data, that's what I'd expect, too.)

Upvotes: 2

Quasdunk
Quasdunk

Reputation: 15220

From your description, the query should be:

SELECT c.customer_id, c.name, ps.section_id
FROM _customers c
  INNER JOIN _projects p
     ON p.customer_id = c.customer_id 
  INNER JOIN _project_sections ps
    ON ps.section_id = p.project_ID 
  WHERE c.active = 1 AND ps.section_id = 1
GROUP BY c.name
ORDER BY LOWER(c.name)

The constraint for the specific section goes in the WHERE clause and not in the JOIN.

Upvotes: 1

Related Questions