mysql - SQL - Find AT LEAST TWO DISTINCT / SEPARATE / DIFFERENT values on another Table -


while taking online database course (for beginner) problem has came attention, had find queries involving ...at least 2 distinct values... example,

the company database in elmasri book states: find employee work on @ least 2 distinct projects. , solution (which works great) is

select distinct lname employee e1 join works_on w1 on (e1.ssn = w1.essn) join works_on w2 on (e1.ssn = w2.essn) w1.pno <> w2.pno

similarly in case of student/course database (i forgot source): find student_id of students take @ least 2 distinct courses. , solution looks simple (though not tested)

select e1.student_id enroll e1, enroll e2 e1.student_id = e2.student_id , e1.course_id <> e2.course_id

in problem, have find name , customer id of customers have accounts in @ least 2 branches of distinct types (i.e., not have same branch type). following table (mysql)

customer:            branch:             account: cust_id  lname       br_id  br_type      acc_num  br_id  cust_id  balance -------  ------      -----  -------      -------  -----  -------  ------- 1        mr.a        10     big          1001     10     1        2000 2        mr.b        11     small        1002     11     1        2500   3        mr.c        12     big          1003     13     1        3000 4        mr.d        13     small        1004     12     2        4000                                           1005     13     3        4500                                          1006     10     4        5000                                          1007     12     4        6000

result table should following:

lname  cust_id -----  ------- mr.a   1

only mr.a has account in branch type 'big' in branch type 'small'

i tried following didnt work

select distinct c1.lname, a1.cust_id customer c1 join account a1 on (c1.cust_id=a1.cust_id) join branch b1 on (a1.br_id=b1.br_id) join branch b2 on (a1.br_id=b2.br_id) b1.br_type<>b2.br_type;

what doing wrong? sorry such long description wanted make sure question understandable , little explanation on < > part highly appreciated.

you're trying pull 2 different branch records off same account record - can't happen. want search on 2 different account records associated branches of different type:

select distinct c1.lname, a1.cust_id customer c1 join account a1 on (c1.cust_id=a1.cust_id) join account a2 on (c1.cust_id=a2.cust_id) join branch b1 on (a1.br_id=b1.br_id) join branch b2 on (a2.br_id=b2.br_id) b1.br_type<>b2.br_type;

sqlfiddle here

a more efficient approach gives same result, use group , having count(distinct br_type) >= 2 - @gordonlindoff proposed.


Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

android - Keyboard hides my half of edit-text and button below it even in scroll view -

i having trouble keyboard covers edit-text , half of button in scroll view. here's layout , please tell me how should resolve problem. many questions on topic none of them s working me. <?xml version="1.0" encoding="utf-8"?> <linearlayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:background="@drawable/bg" android:layout_height="match_parent"> <scrollview android:layout_width="match_parent" android:fillviewport="true" android:layout_height="match_parent"> <relativelayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"> <linearlayout android:layout_width="match_parent" android:layout_height="w...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more