MySQL Query with multiple joins

Question

I have below table, and trying to retrieve the data where at-least one column (lay,blk,pri,ani) should have '1863', but the result is not as desired.

Here i am posting the Table for reference.

DROP TABLE IF EXISTS my_table; 
CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT UNIQUE,
Shot VARCHAR(4),
lay VARCHAR(15) NOT NULL,
lay_status VARCHAR(15) NOT NULL,
blk VARCHAR(10) NOT NULL,
blk_status VARCHAR(15) NOT NULL,
pri VARCHAR(10) NOT NULL,
pri_status VARCHAR(15) NOT NULL,
ani VARCHAR(10) NOT NULL,
ani_status VARCHAR(15) NOT NULL
);

INSERT INTO my_table VALUES
(1,'SH01','1863','yes','1863','yes','P4645','yes','P4557','yes'),
(2,'SH02','1863','yes','P4645','no','P4557','yes','1863','no'),
(3,'SH03','P4645','yes','P4557','yes','1863','yes','1863','yes'),
(4,'SH04','1863','no','P4645','no','P4557','yes','1863','yes'),
(5,'SH05','1863','yes','1863','yes','P4645','yes','P4557','yes'),
(6,'SH06','P4557','yes','P4645','yes','P4645','yes','P4557','yes');

SELECT * FROM my_table;
+----+------+-------+------------+-------+------------+-------+------------+-------+------------+
| id | Shot | lay   | lay_status | blk   | blk_status | pri   | pri_status | ani   | ani_status |
+----+------+-------+------------+-------+------------+-------+------------+-------+------------+
|  1 | SH01 | 1863  | yes        | 1863  | yes        | P4645 | yes        | P4557 | yes        |
|  2 | SH02 | 1863  | yes        | P4645 | no         | P4557 | yes        | 1863  | no         |
|  3 | SH03 | P4645 | yes        | P4557 | yes        | 1863  | yes        | 1863  | yes        |
|  4 | SH04 | 1863  | no         | P4645 | no         | P4557 | yes        | 1863  | yes        |
|  5 | SH05 | 1863  | yes        | 1863  | yes        | P4645 | yes        | P4557 | yes        |
|  6 | SH06 | P4557 | yes        | P4645 | yes        | P4645 | yes        | P4557 | yes        |
+----+------+-------+------------+-------+------------+-------+------------+-------+------------+
6 rows in set (0.00 sec)

I have my created a Query and its result is as below

SELECT 
    x.Shot,
    IFNULL(J1.Stage,'NA') as Stage1,
    IFNULL(J1.status,'NA') as Status,
    IFNULL(J2.Stage,'NA') as Stage2,
    IFNULL(J2.status,'NA') as Status,
    IFNULL(J3.Stage,'NA') as Stage3,
    IFNULL(J3.status,'NA') as Status,
    IFNULL(J4.Stage,'NA') as Stage4,
    IFNULL(J4.status,'NA') as Status
FROM
    (SELECT 
        Shot
    FROM
        my_table) x
    LEFT JOIN (SELECT 
        Shot,
        lay as Artist,
        lay_status as status,
        'lay' as Stage

    FROM
        my_table where lay = '1863') J1 ON J1.Shot = x.Shot
    LEFT JOIN (SELECT 
        Shot,
        blk as Artist,
        blk_status as status,
        'blk' as Stage

    FROM
        my_table where blk = '1863') J2 ON J2.Shot = x.Shot
    LEFT JOIN (SELECT 
        Shot,
        pri as Artist,
        pri_status as status,
        'pri' as Stage

    FROM
        my_table where pri = '1863') J3 ON J3.Shot = x.Shot
    LEFT JOIN (SELECT 
        Shot,
        ani as Artist,
        ani_status as status,
        'ani' as Stage

    FROM
        my_table where ani = '1863') J4 ON J4.Shot = x.Shot

The result of the above query is

+------+--------+--------+--------+--------+--------+--------+--------+--------+
| Shot | Stage1 | Status | Stage2 | Status | Stage3 | Status | Stage4 | Status |
+------+--------+--------+--------+--------+--------+--------+--------+--------+
| SH01 | lay    | yes    | blk    | yes    | NA     | NA     | NA     | NA     |
| SH02 | lay    | yes    | NA     | NA     | NA     | NA     | ani    | no     |
| SH03 | NA     | NA     | NA     | NA     | pri    | yes    | ani    | yes    |
| SH04 | lay    | no     | NA     | NA     | NA     | NA     | ani    | yes    |
| SH05 | lay    | yes    | blk    | yes    | NA     | na     | NA     | NA     |
| SH06 | NA     | NA     | NA     | NA     | NA     | NA     | NA     | NA     |
+------+--------+--------+--------+--------+--------+--------+--------+--------+

But i need to retrieve the records where at-least one among the lay,blk,pri,ani has '1863', but the above query has returned the record with SH06 where non of the fields (lay,blk,pri,ani) has '1863'

required result is like below

+------+--------+--------+--------+--------+--------+--------+--------+--------+
| Shot | Stage1 | Status | Stage2 | Status | Stage3 | Status | Stage4 | Status |
+------+--------+--------+--------+--------+--------+--------+--------+--------+
| SH01 | lay    | yes    | blk    | yes    | NA     | NA     | NA     | NA     |
| SH02 | lay    | yes    | NA     | NA     | NA     | NA     | ani    | no     |
| SH03 | NA     | NA     | NA     | NA     | pri    | yes    | ani    | yes    |
| SH04 | lay    | no     | NA     | NA     | NA     | NA     | ani    | yes    |
| SH05 | lay    | yes    | blk    | yes    | NA     | NA     | NA     | NA     |
+------+--------+--------+--------+--------+--------+--------+--------+--------+

David Martin · Accepted Answer · 2012-12-20 12:37:53Z

1

Try this, although I think your expected result for Status 3 may be wrong:

SELECT
  Shot,
  CASE lay WHEN 1863 THEN 'lay' ELSE 'NA' END AS 'Stage1',
  CASE WHEN lay=1863 THEN lay_status ELSE 'NA' END AS 'Status-lay',
  CASE blk WHEN 1863 THEN 'blk' ELSE 'NA' END AS 'Stage2',
  CASE WHEN blk=1863 THEN blk_status ELSE 'NA' END AS 'Status-blk',
  CASE pri WHEN 1863 THEN 'pri' ELSE 'NA' END AS 'Stage3',
  CASE WHEN pri=1863 THEN pri_status ELSE 'NA' END AS 'Status-pri',
  CASE ani WHEN 1863 THEN 'ani' ELSE 'NA' END AS 'Stage4',
  CASE WHEN ani=1863 THEN ani_status ELSE 'NA' END AS 'Status-ani'
FROM
  my_table
WHERE
  lay = 1863
OR
  blk = 1863
OR
  pri = 1863
OR
  ani = 1863

This gives the following results:

| SHOT | STAGE1 | STATUS-LAY | STAGE2 | STATUS-BLK | STAGE3 | STATUS-PRI | STAGE4 | STATUS-ANI |
------------------------------------------------------------------------------------------------
| SH01 |    lay |        yes |    blk |        yes |     NA |         NA |     NA |         NA |
| SH02 |    lay |        yes |     NA |         NA |     NA |         NA |    ani |         no |
| SH03 |     NA |         NA |     NA |         NA |    pri |        yes |    ani |        yes |
| SH04 |    lay |         no |     NA |         NA |     NA |         NA |    ani |        yes |
| SH05 |    lay |        yes |    blk |        yes |     NA |         NA |     NA |         NA |

http://sqlfiddle.com/#!2/ef7d9/31/0

answered Dec 20, 2012 at 12:37

David Martin

12.4k2 gold badges67 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rao Over a year ago

You are right there was a mistake in my SQL statement it should be J3.Shot = x.Shot instead i kept J2.Shot = x.Shot. Thx for correcting me. Your solution worked +1...

Collectives™ on Stack Overflow

MySQL Query with multiple joins

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related