Mysql Right join with mutiple condtion not working

MySQL

i have two table table1 and table2 and when right joining the table2 with table1 and i am not getting the row. here i have attached the fiddle link also

CREATE TABLE `table1` (
 `orderid` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`orderid`)
);
CREATE TABLE `table2` ( `fields_id` INT NOT NULL AUTO_INCREMENT , `table1_order_id` INT(11) NOT NULL , `field_value` VARCHAR(100) NOT NULL , `fname` VARCHAR(100) NOT NULL , PRIMARY KEY (`fields_id`)) ENGINE = InnoDB;

INSERT INTO `table1` (`orderid`) VALUES (1), (2), (3), (4), (5), (6);
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '1', 'karthi', 'name');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '2', 'karthi', 'name');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '3', 'selva', 'name');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '1', 'salem', 'city');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '2', 'chennai', 'city');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '3', 'mumbai', 'city');

select table1.*
from table1 
right JOIN table2 ON (
        table2.table1_order_id = table1.orderid 
        AND 
            ( table2.field_value LIKE '%karthi%' 
            AND table2.fname = 'name' ) 
        AND ( 
            table2.field_value LIKE '%salem%' 
            AND table2.fname = 'city' 
        ) 

    ) 

    where 1 group by table1.orderid

Above i wrote select query but it returns null result, but i am expecting output like below,

orderid 1

Best Answer

table2.fname cannot be both 'city' and 'name' in the same row.

To get both of this EAV model we use two joins on the table2 with a different alias.

Its unclear why you want a RIGHT JOIN. Normal [inner] JOIN is fine.

I've moved the selection to a WHERE criteria just for ease of readability. Because its inner it could be put in the ON criteria.

While you can group by table1.orderid when you do this you cannot SELECT table1.* because by grouping their is only one row per orderid and therefore multiple orders matching the same critera cannot return on the same line (without an aggregate function like GROUP_CONCAT).

select table1.*
from table1 
inner JOIN table2 t2name ON 
        t2name.table1_order_id = table1.orderid 
inner JOIN table2 t2city ON 
        t2city.table1_order_id = table1.orderid 

where
    t2name.fname = 'name' 
    AND t2name.field_value LIKE '%karthi%' 
    AND t2city.fname = 'city'
    AND t2city.field_value LIKE '%salem%'

or

select table1.orderid
from table1 
inner JOIN table2 t2name ON 
        t2name.table1_order_id = table1.orderid 
inner JOIN table2 t2city ON 
        t2city.table1_order_id = table1.orderid     
where
    t2name.fname = 'name' 
    AND t2name.field_value LIKE '%karthi%' 
    AND t2city.fname = 'city'
    AND t2city.field_value LIKE '%salem%' 
group by table1.orderid
Related Question