CODEWITHSUNDEEP
mysql
gesanri
gesanri

Reputation: 49

MySql: how to get the desired result

I've a table like this:

CREATE TABLE `base_build_floor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `build_no` varchar(64) NOT NULL,
  `build_name` varchar(64) DEFAULT NULL,
  `floor_no` varchar(64) DEFAULT NULL,
  `floor_name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

and insert some data:

INSERT INTO `base_build_floor` VALUES ('41', 'BUILD40210011', 'A', null, null);
INSERT INTO `base_build_floor` VALUES ('42', 'BUILD40210012', 'B', null, null);
INSERT INTO `base_build_floor` VALUES ('43', 'BUILD40210013', 'C', null, null);
INSERT INTO `base_build_floor` VALUES ('44', 'BUILD40210013', 'C', 'FLOOR40210002', 'C1');
INSERT INTO `base_build_floor` VALUES ('45', 'BUILD40210013', 'C', 'FLOOR40210003', 'C2');
INSERT INTO `base_build_floor` VALUES ('46', 'BUILD40210012', 'B', 'FLOOR40210004', 'B1');

the table is about a build-floor table, first you should make a building, then, a building can has no or some floors. the A building has no floor, the B building has one floor named B1, the C building has two floors named C1 and C2, I want to get the result as below:

41 BUILD40210011 A null null
44 BUILD40210013 C FLOOR40210002 C1
45 BUILD40210013 C FLOOR40210003 C2
46 BUILD40210012 B FLOOR40210004 B1

it means that, if a building has no floors, then get it, while if a building has any one floor, the building itself should not be got, so how to write the mysql?I've tried to use Subquery but doesn't work

I've try like this :

SELECT
    b.*
FROM
    base_build_floor b
WHERE
    b.floor_no IS NOT NULL
OR (
    b.floor_no IS NULL
    AND b.build_no NOT IN (
        SELECT
            GROUP_CONCAT(nostr)
        FROM
            (
            SELECT
                concat("'", f.build_no, "'") as nostr
            FROM
                base_build_floor f
            WHERE
                f.floor_no IS NOT NULL
            GROUP BY
                f.build_no
        ) t
)

)

but I get all the data

Upvotes: 1

Views: 27

Answers (2)

forpas
forpas

Reputation: 164099

With NOT EXISTS:

select t.* from base_build_floor t
where t.floor_no is not null
or not exists (
  select 1 from base_build_floor
  where build_no = t.build_no and floor_no is not null
)

See the demo.
Results:

| id  | build_no      | build_name | floor_no      | floor_name |
| --- | ------------- | ---------- | ------------- | ---------- |
| 41  | BUILD40210011 | A          |               |            |
| 44  | BUILD40210013 | C          | FLOOR40210002 | C1         |
| 45  | BUILD40210013 | C          | FLOOR40210003 | C2         |
| 46  | BUILD40210012 | B          | FLOOR40210004 | B1         |

Upvotes: 2

Aioros
Aioros

Reputation: 4383

This query would be much simpler if you had normalized tables. Ideally, you would have a buildings table with building id, no, and name, and a floors table with building id, floor no, and floor name. Then you could just join the two tables. Since that's not the case, we can basically extract the building and floor sub-tables from the main one and join them like this:

SELECT
    b.build_no,
    b.build_name,
    f.floor_no,
    f.floor_name
FROM
    (SELECT DISTINCT build_no, build_name
       FROM base_build_floor) b
LEFT OUTER JOIN
    (SELECT *
       FROM base_build_floor
      WHERE floor_no IS NOT NULL) f ON b.build_no = f.build_no

Upvotes: 0

Related Questions