mysql - How to do multiple join on same table and then join with another table? -
i want inner join on same table i.e product_attributes want prod_value = gender , prod_attr=male , maingroup = pants. means, want products gender male , falls under pants .i want print prodgroup , join product_master table need prod_name. how can achieve this?
table 1 : product_attributes
+----+------------+-----------+------------+ | id | prod_style | prod_attr | prod_value | +----+------------+-----------+------------+ | 1 | 0010 | gender | male | | 2 | 0010 | maingroup | pants | | 3 | 0010 | prodgroup | pants_abc | | 4 | 0010 | blue | color | | 5 | 0011 | gender | male | | 6 | 0011 | maingroup | pants | | 7 | 0011 | prodgroup | pants_pqr | | 8 | 0012 | gender | female | | 9 | 0012 | maingroup | pants | | 10 | 0012 | prodgroup | pants | | 11 | 0013 | gender | female | | 12 | 0013 | maingroup | jackets | +----+------------+-----------+------------+ table 2 :product_master
+----+------------+-----------+ | id | prod_style | prod_name | +----+------------+-----------+ | 1 | 0010 | abc | | 2 | 0011 | pqr | | 3 | 0012 | xyz | | 4 | 0013 | lmn | +----+------------+-----------+ i have tried solution:
select * product_master pm inner join (select * product_attributes prod_value='prodgroup' , prod_style in(select prod_style product_attributes prod_attr ='pants' , prod_value='maingroup' , prod_style in(select prod_style product_attributes prod_attr='male' ) ) ) p on pm.prod_style = p.prod_style order prod_name with solution getting output don't know whether correct way of writing query.
output using above solution :
+-----------+------------+-----------+-------------+-----------+-----------+ | id | prod_style | prod_name | prod_style | prod_attr | prod_value| +-----------+------------+-----------+-------------+-----------+-----------+ | 1 | 0010 | abc |0010 |pants_abc |prodgroup | | 2 | 0011 | pqr |0011 |pants_pqr |prodgroup | | 3 | 0012 | xyz |0012 |pants |prodgroup | | 4 | 0013 | lmn |0013 |skinny |prodgroup | +-----------+------------+-----------+-------------------------------------+
i've built query:
declare @product_attributes table ( id int , prod_style varchar(10) , prod_attr varchar(10) , prod_value varchar(10) ); declare @product_master table ( id int , prod_style varchar(10) , prod_name varchar(10) ); insert @product_attributes (id, prod_style, prod_attr, prod_value) values (1, '0010', 'gender','male') , (2, '0010', 'maingroup','pants') , (3, '0010', 'prodgroup', 'pants_abc') , (4, '0010', 'blue', 'color') , (5, '0011', 'gender', 'male') , (6, '0011', 'maingroup', 'pants') , (7, '0011', 'prodgroup', 'pants_pqr') , (8, '0012', 'gender', 'female') , (9, '0012', 'maingroup', 'pants') , (10, '0012', 'prodgroup', 'pants') , (11, '0013', 'gender', 'female') , (12, '0013', 'maingroup', 'jackets'); insert @product_master (id, prod_style, prod_name) values (1, '0010', 'abc') , (2, '0011', 'pqr') , (3, '0012', 'xyz') , (4, '0013', 'lmn'); select pa1.id, pa1.prod_style, pm.prod_name, pa1.prod_attr, pa1.prod_value @product_attributes pa1 inner join @product_master pm on pm.prod_style = pa1.prod_style pa1.prod_attr = 'maingroup' , pa1.prod_value = 'pants' , exists ( select 1 @product_attributes pa2 pa2.prod_style = pa1.prod_style , pa2.prod_attr = 'gender' , pa2.prod_value = 'male' ); it checks if product (based on prod_style) males , pants. both conditions must met, has different output yours, is:
╔════╦════════════╦═══════════╦═══════════╦════════════╗ ║ id ║ prod_style ║ prod_name ║ prod_attr ║ prod_value ║ ╠════╬════════════╬═══════════╬═══════════╬════════════╣ ║ 2 ║ 0010 ║ abc ║ maingroup ║ pants ║ ║ 6 ║ 0011 ║ pqr ║ maingroup ║ pants ║ ╚════╩════════════╩═══════════╩═══════════╩════════════╝ did lose in logic? please let me know.
Comments
Post a Comment