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

Popular posts from this blog

javascript - Chart.js (Radar Chart) different scaleLineColor for each scaleLine -

-
Image
i try , create radar chart using chart.js has various colours each scaleline, or coloured between scalelines. wondering if possible? from: to: i have working graph, though there doesn't seem method change individual scale lines. kind regards leigh you can extend radar chart type this, so chart.types.radar.extend({ name: "radaralt", initialize: function (data) { chart.types.radar.prototype.initialize.apply(this, arguments); var originalscaledraw = this.scale.draw; var ctx = this.chart.ctx; this.scale.draw = function () { var linewidth = this.linewidth; // bypasses line drawing in originalscaledraw this.linewidth = linewidth; originalscaledraw.apply(this, arguments); ctx.linewidth = this.linewidth; var scale = this; // draw chart.helpers.each(scale.ylabels, function (label, index) { // color of ...
Read more

apache - Error with PHP mail(): Multiple or malformed newlines found in additional_header -

-
suddenly have started receiving above error without changes having been made script. host 1and1 (i know...) the script still works fine on different server, , suspicion there must have been server config change has lead this, although hosts plead ignorance. there's no information on above error @ in google can find - have ideas? server running apache if helps. had similar problem. came out of blue. no php code changed. what changed: php upgraded 5.5.25-1 5.5.26. a security risk in php mail() function has been fixed , newlines in additional_headers allowed no more. because newlines mean: starts email message (and surely don't want inject newlines through headers followed evil message). what have worked fine, e.g. having newlines after headers or passing whole message additional_headers , function no more. solution : sanitize headers. no multiple newlines in additional_headers argument. these count "multiple or malformed newlines":...
Read more

java - Android – MapFragment overlay button shadow, just like MyLocation button -

-
Image
i'm struggling add shadow custom overlay button on google map. goal make custom button google's mylocation button. here's how app looks now: layout.xml <relativelayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" xmlns:map="http://schemas.android.com/apk/res-auto" android:layout_height="match_parent" android:layout_width="match_parent" style="@style/apptheme"> <fragment android:layout_width="match_parent" android:layout_height="match_parent" android:id="@+id/map" tools:context=".mapsactivity" android:name="com.google.android.gms.maps.supportmapfragment" android:layout_alignparentend="true" android:layout_alignparentstart="true" android:layout_alignparentbottom="true" ...
Read more