CODEWITHSUNDEEP
mysql
Prasanna
Prasanna

Reputation: 1751

multi level query in sql

I want a query on 3 tables which will give me a result like below:

I've USERID depending on this I want to fetch the data from the table below:

ANSTABLE

 ID | ANS | USERID | QUERYID
  1 | 123 |   1    | 15
  2 | 22  |   0    | 16
  3 | 17  |   1    | 10

ID from ANSTABLE is mapped with ANSID in ANSVOTABLE

ANSVOTABLE

 USERID | QUERYID | ANSID | UPVOTE | DOWNVOTE
    3   |    15   |   1   |   1    |    0 
    8   |    15   |   1   |   0    |   -1 
    7   |    15   |   1   |   0    |   -1
    22  |    16   |   2   |   1    |    0

QUERYID id mapped with ID from 'QUERYTABLE'

QUERYTABLE

 ID | USERID | QUERY   | DESCRIPTION
 16 |  10    | qwerty  | uytrew
 15 |  11    | my_data | test_data
 10 |  0     | 101010  | 101010

Now I want a result like, whenever I give USERID - 0 OR 1 etc. it should fetch all queryID's related to entered USERID from ANSTABLE and depending on matched queryID's all records should be returned from QUERYTABLE table and also it should give me SUM(UPVOTE)+SUM(DOWNVOTE) as total corresponding to ANSID && QUERYID from ANSVOTABLE.

In fact i want result like below if i enter `userID - 1 it should give me by adding one more column

 ID | USERID | QUERY   | DESCRIPTION | ANSSCORE
 15 |  11    | my_data | test_data   |   -1
 10 |  0     | 101010  | 101010      |    0

Please note that ANSSCORE becomes -1 from the result of ANSVOTABLE by calculating upvote and downvote.

Also consider if corresponding AnsID and QueryId is not existing in ANSVOTABLE then in that case it should return me record from Querytable with o as total score

Answers will be appriciated.

Upvotes: 0

Views: 2688

Answers (2)

Ankush Rathi
Ankush Rathi

Reputation: 620

You need to join those 3 tables on the keys and then do a 2 or 3rd-degree group by with whatever where clause you have as requirement.

I think this might work for you -

SELECT q.ID, q.QUERY, q.USERID, vote.UPVOTE + vote.DOWNVOTE as totalVote 
FROM QUERYTABLE q
JOIN ANSTABLE ans ON ans.QUERYID = q.ID
JOIN ANSVOTABLE vote ON vote.ANSID = ans.ID
WHERE ans.USERID = 2
GROUP BY q.ID, q.QUERY, q.USERID;

Upvotes: 1

Zynon Putney II
Zynon Putney II

Reputation: 695

You just need to sum the two columns and group by the other columns you want. You've already defined the table relationships so this should be fairly straightforward unless I'm misunderstanding something.

create table anstable(
ID number,
ANS number,
USERID number,
QUERYID number);

Insert into anstable values(1, 123, 1, 15);
Insert into anstable values(2, 22, 0, 16);
Insert into anstable values(3, 17, 1, 10);

create table ANSVOTABLE(
USERID number,
QUERYID number,
ANSID number,
UPVOTE number,
DOWNVOTE number);

Insert into ansvotable values(3, 15, 1, 1, 0);
Insert into ansvotable values(8, 15, 1, 0, -1);
Insert into ansvotable values(7, 15, 1, 0, -1);
Insert into ansvotable values(22, 16, 2, 1, 0); 

create table QUERYTABLE(
ID number,
USERID number,
QUERY varchar2(50),
DESCRIPTION varchar2(50));

insert into querytable values(16, 10, 'qwerty', 'uytrew');
insert into querytable values(15,  11, 'my_data', 'test_data');
insert into querytable values(10,  0, '101010', '101010');

select a.id, a.userid, a.query, a.description, sum(b.upvote + b.downvote) as ansscore
from querytable a
join ansvotable b on b.queryid = a.id
join anstable c on c.id = b.ansid
where c.userid in (0, 1)
group by a.id, a.userid, a.query, a.description;

Results:

Table created.

1 row(s) inserted.

1 row(s) inserted.

1 row(s) inserted.

Table created.

1 row(s) inserted.

1 row(s) inserted.

1 row(s) inserted.

1 row(s) inserted.

Table created.

1 row(s) inserted.

1 row(s) inserted.

1 row(s) inserted.

Result Set 1
ID  USERID  QUERY   DESCRIPTION ANSSCORE
15  11  my_data test_data   -1
16  10  qwerty  uytrew  1

Upvotes: 1

Related Questions