MySQL nested queries problems and optimisations

Question

I have database of scientific conferences.

Conferencis held in universitis
Every student can visit any conferences.
Every university have few student

CREATE TABLE uni #University
(
  region          varchar(255) NOT NULL,
  name            varchar(255) NOT NULL,
  id              int unsigned NOT NULL auto_increment,     
  PRIMARY KEY     (id)
);

CREATE TABLE student 
(
    uni_id          int unsigned NOT NULL,
    name            varchar(255) NOT NULL,
    id              int unsigned NOT NULL auto_increment,
    PRIMARY KEY     (id),
    FOREIGN KEY (uni_id) REFERENCES uni (id)
);

CREATE TABLE conf #Conference
(
    uni_id          int unsigned NOT NULL,
    name            varchar(255) NOT NULL, 
    id              int unsigned NOT NULL auto_increment,
    PRIMARY KEY     (id),
    FOREIGN KEY     (uni_id) REFERENCES uni (id)
);


CREATE TABLE visits #table participants
# id_student visit conference id_conf and maybe have winnner place
(
    id_conf         int unsigned NOT NULL, 
    id_student      int unsigned NOT NULL,
    place           int unsigned, #1..3
    PRIMARY KEY     (id_conf, id_student),

    FOREIGN KEY (id_conf) REFERENCES conf (id),
    FOREIGN KEY (id_student) REFERENCES student (id)
);

I need code 5 select requests:

1)get names of students who visit conference "DataBase 2015"

SELECT vc.name FROM
    (SELECT * FROM visits v 
        INNER JOIN conf c 
        ON (v.id_conf = c.id)) vc # visits with names of conference
    INNER JOIN student s 
    ON (vc.id_student = s.id)
    WHERE vc.name = "DataBase 2015";

2) get id of unisersities which students have winners places on conference "DataBase 2015"

SELECT DISTINCT uni_id 
    FROM student s 
    INNER JOIN 
          (SELECT id_student 
                  FROM visits v 
                  INNER JOIN conf c 
                  ON (v.id_conf = c.id) 
                  WHERE (v.place is NOT NULL and 
                         c.name = "DataBase 2015")
           ) winers
    ON (winers.id = i.id_student);

3) Get university id where held more then 1 conferences

SELECT uni_id FROM conf c GROUP BY c.uni_id having COUNT(*) > 1;

4) Get university which student have places in ALL conferences. It means we need compare number of all conferences and number of conferences in which students of some uni have any win place

Need write something like this(java):

uni_list.stream().filter( uni -> {
        Set<Conference> id_have_winners = new new HashSet<>;
        for(Student s : getStudents(uni.getId()) {
            for(Conference c : conferences) {
               if (studentWinConference(s, c)
                   id_have_winners.put(c);
        }
        bool haveWinnersInAllConferences = id_have_winners.size() == conferences.size();
        return haveWinnersInAllConferences;
}

5) get Uni which students visit only one conference.

SELECT id_conf, id_student, uni_id FROM 
    visits v INNER JOIN student s ON (s.id = v.id_student) GROUP BY s.uni_id having COUNT(v.id_conf) = 1

6) get Uni which students visit some conferences but nobody won

1, 2, 3 are works, but I need сouncil for possible simplifications; 4, 5, 6 is hard and I would be glad to any tips or code

What SQL have you got for part of 4, 5 and 6? See [this recent answer](http://stackoverflow.com/a/34327197/3404097). — philipxy, Dec 17 '15 at 23:18
@philipxy I dont know how code 4, 5, 6. Need some help or instructions with it — punksta, Dec 17 '15 at 23:30
For 1,2,3: none of those subqueries are required. You just need regular joins with the appropriate filters in the WHERE clause. For 4,5,6: Look into aggregate queries. — Rabbit, Dec 17 '15 at 23:54
@Rabbit Thanks for remark. I added 5. It returns uni id. Can I get uni name? — punksta, Dec 18 '15 at 00:35

score 1 · Accepted Answer · answered Dec 18 '15 at 04:20

Try these queries. You are welcome to add some data to http://sqlfiddle.com/#!9/12247/16 example and let me know if something doesn't work. Include expected output also.

Name of students who visit conference "DataBase 2015"

select distinct s.name
from visits v
inner join conf c on v.id_conf = c.id
inner join student s on v.id_student = s.id
where c.name = 'DataBase 2015'

ID of universities with winning students for "DataBase 2015"

select distinct u.id
from visits v
inner join conf c on v.id_conf = c.id
inner join student s on v.id_student = s.id
inner join uni u on s.uni_id = u.id
where place is not null
and c.name = 'Database 2015'

University ID where more than 1 conference was held

select uni_id
from conf
group by uni_id
having count(*) > 1

University with students placed in all conferences

select distinct s.uni_id
from student s
left join (
  select id_conf, id_student, uni_id
  from visits v 
  inner join conf c on v.id_conf = c.id
  where place is not null
) data on data.id_student = s.id and data.uni_id = s.uni_id
where data.id_student is not null

University students visited only one conference

select s.uni_id
from visits v
inner join conf c on v.id_conf = c.id
inner join student s on v.id_student = s.id
group by s.uni_id
having count(*) = 1

University whose students weren't placed

select distinct s.uni_id
from student s
left join (
  select id_conf, id_student, uni_id
  from visits v 
  inner join conf c on v.id_conf = c.id
  where place is null
) data on data.id_student = s.id and data.uni_id = s.uni_id
where data.id_student is not null

MySQL nested queries problems and optimisations

1 Answers1