sql - Mysql recursive query in -


friends. mysql db has 4 tables products, product_properties, properties, property_values

products

|id|name| |1 |book1| |2 |book2| |3 |book3|

...

properties

|id|name| |1 |color| |2 |binding|

...

property_values

|id|property_id|value  | |1 |1          | red   | |2 |1          | orange| |3 |1          | green | |4 |2          | soft  | |5 |2          | hard  |

product_properties

|id|product_id|property_id| value | |1 |1         |1          | 1 |2 |1         |2          | 4 |3 |2         |1          | 1 |4 |2         |2          | 4 |5 |3         |1          | 2

i need list of products have color red , binding soft. i'm confused.

select products.id products  join product_properties on product_properties.product_id = products.id  join properties on properties.id = product_properties.property_id join property_values on property_values.id = product_properties.value properties.name = 'color' , property_values.value = 'red' , properties.name = 'binding' , property_values.value = 'soft'

...not working...

you close:

select pp.id product_properties pp join      properties p      on p.id = pp.property_id join      property_values pv      on pv.id = pp.value (p.name = 'color' , pv.value = 'red') or       (p.name = 'binding' , pv.value = 'soft') group pp.id having count(distinct p.name) = 2;

note: removed products table, because not needed query.

this selects properties meet conditions. selects products have 2 matches.


Comments

Post a Comment

Popular posts from this blog

PHP and MySQL WP -

i trying accomplish 2 things here code. writing php directly wp page using insert php plugin. 1) query mysql database , return results 1 column 4 columns. 2) make each result own hyperlink directs user new wp page runs new query on page. i able query show results , turn results dynamic hyperlink, cannot formatting down. right returns result 1 column. here have: [insert_php] $servername = "localhost"; $username = "login"; //edited privacy $password = "password"; //edited privacy $database = "database"; //edited privacy // create connection $conn = new mysqli($servername, $username, $password, $database); // check connection if ($conn->connect_error) { die("connection failed: " . $conn->connect_error); } echo "connected successfully"; //get results database $result = mysqli_query($conn,"select mine mines order mine"); $all_property = array(); //declare array saving property //showing proper...
Read more

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

Why does math.random(999999999999) returns 1 in Lua? -

i tried: print(math.random(999999999999)) , has printed 1 . also math.random() includes 999999999999 printing same thing. examples: print(math.random(1.999999999999)) » 1 print(math.random(1999999999999)) » 1 for k,v in next,{math.random(999999999999), math.random(1999999999999), math.random(2.999999999999)} print(v) end » 1 local n = math.random(999999999999) print(n==1) » true then think understood (if know lua of course). can explain me? @edits: the lua version i'm using 5.2. i tried print(math.random(-999999999999)) , printed 111711452 . looks worked positive number. this problem surely because math.random treats input arguments passed in lua 5.1 and/or 5.2. [mathlib.c] 1 : as may know in c , standard int can represent values -2,147,483,648 2,147,483,647 . adding +1 2,147,483,647 , in use-case, overflow , wrap around value giving -2,147,483,648 . end result negative since you're multiplying positive negative number. ther...
Read more