CODEWITHSUNDEEP
mysql
Murad Hasan
Murad Hasan

Reputation: 9583

MySql - Get field from table 2, If not then from table 1

I have two table complain and repair. I want to get ass_to_per [Latest one] from repair or from complain if the id of complain is not present in repair.

Explanation:

I want to get the ass_to_per from the complain table, and also from the repair table. But there is a relation between them, in repair there is a field called com_id which is a foreign key. So i want to get the ass_to_per from complain and also check the repair for the foreign key, if any then check the ass_to_per of repair. If any then get it as result.

I have sqlfiddle, For online testing: sqlfiddle.com

The table and data given below.

Complain

CREATE TABLE `complain` (
  `id` int(11) NOT NULL,
  `ass_to_per` varchar(50) NOT NULL
);
INSERT INTO `complain` (`id`, `ass_to_per`) VALUES
(1, 'frayne'),
(2, 'murad'),
(4, 'frayne'),
(5, 'murad'),
(6, 'frayne'),
(7, 'frayne');

Repair

CREATE TABLE `repair` (
  `id` int(11) NOT NULL,
  `com_id` int(11) NOT NULL,
  `ass_to_per` varchar(50) NOT NULL
);
INSERT INTO `repair` (`id`, `com_id`, `ass_to_per`) VALUES
(1, 1, 'frayne'),
(2, 1, 'murad'),
(3, 4, 'frayne'),
(4, 6, 'murad'),
(5, 2, 'murad'),
(6, 5, 'frayne');

My Query:

SELECT * FROM `complain` 
WHERE `id` IN (SELECT DISTINCT(`com_id`) FROM `repair` WHERE `ass_to_per` = 'frayne') OR `ass_to_per`='frayne'

Query Result

id |    ass_to_per
--------------
1  |    frayne
4  |    frayne
5  |    murad
6  |    frayne
7  |    frayne

Analysis

id | ass_to_per[complain] | ass_to_per[repair]
--------------
1  |    murad             |    frayne
2  |    murad             |    murad
4  |    frayne            |    frayne          //need this one
5  |    murad             |    frayne          //need this one
6  |    frayne            |    murad
7  |    frayne            |                    //need this one

Expected result:

id |    ass_to_per
--------------
4  |    frayne        //ass_to_per from repair
5  |    frayne        //ass_to_per from repair
7  |    frayne        //ass_to_per from complain

Upvotes: 0

Views: 65

Answers (2)

Strawberry
Strawberry

Reputation: 33945

SELECT c.id
  FROM complain c 
  LEFT 
  JOIN 
     ( SELECT x.*
         FROM repair x
         JOIN 
            ( SELECT com_id,MAX(id) id FROM repair GROUP BY com_id ) y
           ON y.com_id = x.com_id
          AND y.id = x.id
     ) r
    ON r.com_id = c.id
 WHERE COALESCE(r.ass_to_per,c.ass_to_per) = 'frayne';

Upvotes: 0

Preuk
Preuk

Reputation: 632

COALESCE() will output the first non-null parameter it finds, so using a join you can probably get ass_to_per from complain or repair depending on which one exists:

SELECT 
    complain.id, 
    COALESCE(repair3.ass_to_per, complain.ass_to_per) as ass_to_per
FROM complain
LEFT JOIN 
    (SELECT max(id) as maxid, com_id FROM repair GROUP BY com_id) as repair2 
    ON complain.id = repair2.com_id
LEFT JOIN repair as repair3
    ON repair2.maxid = repair3.id
GROUP BY complain.id

If you want to further filter (like in your example on 'frayne') on computed ass_to_per, just embed this select as a subquery:

SELECT * 
FROM (
  SELECT 
    complain.id, 
    COALESCE(repair3.ass_to_per, complain.ass_to_per) as ass_to_per
  FROM complain
  LEFT JOIN (SELECT max(id) as maxid, com_id FROM repair GROUP BY com_id) AS repair2 
    ON complain.id = repair2.com_id
  LEFT JOIN repair as repair3
    ON repair2.maxid = repair3.id
  GROUP BY complain.id
) AS mydata 
WHERE mydata.ass_to_per = 'frayne'
ORDER BY mydata.id;

Fiddle here : http://sqlfiddle.com/#!9/33433/49

Upvotes: 1

Related Questions