0

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

1

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
| improve this answer | |
  • Thanks saved my days – karthickeyan Feb 18 at 10:04
  • You're welcome. Also see eav guide for mysql – danblack Feb 18 at 21:03
  • your works fine. thanks for sharing the eav guide. now the problem i need to search on more than 50 column or more on that. – karthickeyan Mar 4 at 5:16
  • I'd seriously start looking at full text search engines like solr and see how they work with mysql datasources. – danblack Mar 4 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.