How to combine three tables result by one SQL query -


i have 3 tables

table "tutor" tutor_id   nmae    --------------- 1          n1 2          n2 3          n3 4          n4  table "tutor_setting" tutor_id   max_stu_num ---------------------- 1          5  2          6  table "stu_tutor_mapping" ---------------------- stu_id   tutor_id         1 b         2 c         1 d         1

and want combile below 3 sql 1 sql:

1. select * tutor t1 2. select * tutor_setting t2 t2.tutor_id=t1.tutor_id 3. select count(t3.stu_id) stu_tutor_mapping t3 t3.tutor_id=t1.tutor_id group t1.tutor_id

and expected result: select table "tutor" , join "tutor_setting" , join "stu_turtor_mapping" , count rows of "stu_tutor_mapping" when there have same tutor_id.

tutor_id    name     max_stu_num    total_stu_num ------------------------------------------------- 1           n1       5              3 2           n2       6              1 3           n3       null 4           n4       null

i tried way:

select t1.tutor_id, t1.name, t2.max_stu_num, count(t3.stu_id) tutor t1    left join tutor_setting t2 on (t1.tutor_id=t2.tutor_id)    left join stu_tutor_mapping t3 on (t1.tutor_id=t3.tutor_id) group t1.tutor_id, t1.name;

but database report error:

ora-00979: not group expression 00979. 00000 -  "not group expression" *cause:     *action: error @ line: 1 column: 30

finally, resolve issue google, here sql statement:

select t1.tutor_id, t1.name, t2.max_stu_num, t3.stu_allocated tutor t1    left join tutor_setting t2 on (t1.tutor_id=t2.tutor_id)    left join     (select tutor_id ,count(*) total_stu_num stu_turtor_mapping group     tutor_id) t3 on t3.tutor_id=t1.tutor_id 

select  a.tutor_id,         a.name,         count(b.tutor_id) totalcount    tutor         left join stu_tutor_mapping b             on a.tutor_id = b.tutor_id group   a.tutor_id, a.name

to further gain more knowledge joins, kindly visit link below:

the query above display records table tutor whether has no matching record on other table , display 0 total count since using left join. if want list records has atleast 1 matching record on other table, inner join suffice.

select  a.tutor_id,         a.name,         count(*) totalcount    tutor         inner join stu_tutor_mapping b             on a.tutor_id = b.tutor_id group   a.tutor_id, a.name

Comments

Post a Comment

Popular posts from this blog

php - cannot display multiple markers in google maps v3 from traceroute result -

i'm newbie in php mysql , google maps v3. have problems in google maps v3. made traceroute web based , map traceroute's result in google maps v3 final project of college education. have try many tutorials didn't work. have tried tutorials in forum nothing worked. problem cannot display multiple markers got location data database. maybe because i'm newbie , not know how do. confuse looping show markers. here code <?php error_reporting(e_all ^ (e_notice)); ini_set('max_execution_time', 360); $enable_log_user = false; global $ip, $host_name, $host_ip; $host = @$_post['host']; $trace = @$_post['trace']; $self = $_server['php_self']; include("phpsqlajax_dbinfo.php"); $connection = mysql_connect ('127.0.0.1', $username, $password); if (!$connection) { die('not connected : ' . mysql_error());} $db_selected = mysql_select_db($database, $connection); if (!$db_selected) { die ('can\'t use db : ...
Read more

c# - DetailsView in ASP.Net - How to add another column on the side/add a control in each row? -

i have detailsview control on page used edit various fields of record, works in respect. i looking way add 1 column (and if works, why not more) right, absolutely read-only, show same fields of record comparison purposes. i aware there no obvious way such thing out of box detailsview . have looked other controls (transposed gridview , recommended formview , listview ), nothing satisfies. have special data binding setup using detailsview , can't out of without losing features. anyone on how "hack in" additional columns (for display only) on detailsview ? the solution have now, use second detailsview , visible set false in aspx. in code, make sure databind hidden detailsview hosts data third column first, initial detailsview named itemdetails . and in item created event, pass third column html rendering of hidden controls (in last code block) : protected void itemdetails_itemcreated(object sender, eventargs e) { if (dataite...
Read more

javascript - firefox memory leak -

i have following script (i use raphaeljs also) draws circle, waits 1 second, draws circle again, etc (you can copy paste - functional, need include raphael raphaeljs.com): <div id="holder"> </div> <script src="raphael.js" type="text/javascript"></script> <script> var paper = raphael(10, 50, 320, 200); var rad = math.pi / 180; var finangle = 0; function sector(cx, cy, r, startangle, endangle, params) { var x1 = cx + r * math.cos(-startangle * rad), x2 = cx + r * math.cos(-endangle * rad), y1 = cy + r * math.sin(-startangle * rad), y2 = cy + r * math.sin(-endangle * rad); return ["m", cx, cy, "l", x1, y1, "a", r, r, 0, +(endangle - startangle > 180), 0, x2, y2]; } var path = sector(100,100,50,0,180); var arc = paper.path(path); function draw() { path = sector(100,100,50,0,finangle); ...
Read more