CODEWITHSUNDEEP
mysqlsqljoin
AdRock
AdRock

Reputation: 3096

MySQL joining tables gives unexpected output

I have been bashing my head against a brick wall trying to get this working and I don't know why it's not.

I join tables A and B using my_field. I then run a sub query to get my_field from table B where complete = 1. This is what I want to use to query tables C and D

This is my current query 

SELECT 
    table_A.*,
    table_B.*,
    table_C.*,
    table_D.*
FROM table_A
    INNER JOIN table_B ON
        table_A.my_field = table_B.my_field
    LEFT JOIN (SELECT my_field FROM table_B WHERE complete ='1') test ON
        table_B.my_field = test.my_field
    RIGHT JOIN table_C ON
        test.my_field = table_C.my_field
    INNER JOIN table_D ON
        table_C.my_field = table_D.my_field

This is the output of the current query

table_A.field1  |   table_A.field2  |   table_B.field1  |   table_B.field2  |   table_C.field1  |   table_C.field2  |   table_D.field1  |   table_D.field2  |   test.complete
=============================================================================================================================================================================
something       |   something       |   something       |   something       |   something       |   something       |   something       |   something       |       1
    null        |       null        |       null        |       null        |   something       |   something       |   something       |   something       |       0

and this is what i want to get

table_A.field1  |   table_A.field2  |   table_B.field1  |   table_B.field2  |   table_C.field1  |   table_C.field2  |   table_D.field1  |   table_D.field2  |   test.complete
=============================================================================================================================================================================
something       |   something       |   something       |   something       |   something       |   something       |   something       |   something       |       1
something       |   something       |   something       |   something       |   null            |   null            |   null            |       null        |       0

UPDATE:

Here is the table structures. I've removed columns that have do not link to any other table

CREATE TABLE IF NOT EXISTS `table_A` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
);

CREATE TABLE IF NOT EXISTS `table_B` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  `complete` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `table_C` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
);

CREATE TABLE IF NOT EXISTS `table_D` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Upvotes: 1

Views: 60

Answers (1)

bowlturner
bowlturner

Reputation: 2016

why don't you get rid of the Left Join? This makes it easier to read.

SELECT 
  table_A.*,
  table_B.*,
  table_C.*,
  table_D.*
FROM table_A
  INNER JOIN table_B ON
    table_A.my_field = table_B.my_field
  LEFT JOIN table_C ON
    table_B.my_field = table_C.my_field and table_B.complete ='1'
  LEFT JOIN table_D ON
    table_C.my_field = table_D.my_field

Upvotes: 1

Related Questions