Thread: sql query problem
Dear All, I have two tables one contains details of user and other contains result. The details are: 1. UserTable - id, name, phone 2. result - id, question_id, user_id, status (0 or 1) I want the list like this: User Id Name Attended Failed Passed but i could not find the way to do this. Please help
What does a 0 state mean? Failed? And a 1 state? Passed?<br /><br />Best,<br />Oliveiros<br /><br /><div class="gmail_quote">2012/1/14Alok Thakur <span dir="ltr"><<a href="mailto:alokthakur1987@gmail.com">alokthakur1987@gmail.com</a>></span><br/><blockquote class="gmail_quote" style="margin:0pt0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Dear All,<br /><br /> I have twotables one contains details of user and other contains<br /> result. The details are:<br /> 1. UserTable - id, name, phone<br/> 2. result - id, question_id, user_id, status (0 or 1)<br /><br /> I want the list like this:<br /> User Id Name Attended Failed Passed<br /><br /> but i could not find the way to do this.<br /><br /> Please help<br /><spanclass="HOEnZb"><font color="#888888"><br /> --<br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br /></font></span></blockquote></div><br/>
It seems question is not clear... I could not determine what should be in column Attended, and based on what should define passed/failed But quick tip would be SELECT name, CASE status WHEN 1 THEN 'Passed' ELSE 'Failed' END FROM UserTable INNER JOIN result ON UserTable.id = result.user_id Sent from my Windows Phone From: Alok Thakur Sent: 15/01/2012 22:08 To: pgsql-sql@postgresql.org Subject: [SQL] sql query problem Dear All, I have two tables one contains details of user and other contains result. The details are: 1. UserTable - id, name, phone 2. result - id, question_id, user_id, status (0 or 1) I want the list like this: User Id Name Attended Failed Passed but i could not find the way to do this. Please help -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
It seems question is not clear... I could not determine what should be in column Attended, and based on what should define passed/failed But quick tip would be SELECT name, CASE status WHEN 1 THEN 'Passed' ELSE 'Failed' END FROM UserTable INNER JOIN result ON UserTable.id = result.user_id Sent from my Windows Phone From: Alok Thakur Sent: 15/01/2012 22:08 To: pgsql-sql@postgresql.org Subject: [SQL] sql query problem Dear All, I have two tables one contains details of user and other contains result. The details are: 1. UserTable - id, name, phone 2. result - id, question_id, user_id, status (0 or 1) I want the list like this: User Id Name Attended Failed Passed but i could not find the way to do this. Please help -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Dear, I am trying to provide you as much details as possible. answer` ( `id` int(10) NOT NULL AUTO_INCREMENT, `question_id` int(10) NOT NULL, `user_id` int(10) NOT NULL, `answer` int(10)NOT NULL, -> `status` tinyint(1) NOT NULL, --> Status will be 0 or 1 means wrong or right answer `date` datetime NOT NULL, PRIMARY KEY (`quiz_result_id`) ) user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user` varchar(255) NOT NULL, `username` varchar(255) NOT NULL, `user_email`varchar(255) NOT NULL, `user_gender` varchar(255) NOT NULL, `refrence` varchar(255) NOT NULL, `join_date` varchar(255)NOT NULL, `status` tinyint(1) NOT NULL, `banned` tinyint(1) NOT NULL, PRIMARY KEY (`user_id`) ) Now I want the report like this: UserID UserName Attended(questions) Wrong Correct 1 A On Jan 16, 3:49 am, misa.si...@gmail.com (Misa Simic) wrote: > It seems question is not clear... > > I could not determine what should be in column Attended, and based on > what should define passed/failed > > But quick tip would be > > SELECT name, CASE status WHEN 1 THEN 'Passed' ELSE 'Failed' END FROM > UserTable INNER JOIN result ON UserTable.id = result.user_id > > Sent from my Windows Phone > From: Alok Thakur > Sent: 15/01/2012 22:08 > To: pgsql-...@postgresql.org > Subject: [SQL] sql query problem > Dear All, > > I have two tables one contains details of user and other contains > result. The details are: > 1. UserTable - id, name, phone > 2. result - id, question_id, user_id, status (0 or 1) > > I want the list like this: > User Id Name Attended Failed Passed > > but i could not find the way to do this. > > Please help > > -- > Sent via pgsql-sql mailing list (pgsql-...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-sql > > -- > Sent via pgsql-sql mailing list (pgsql-...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-sql
Alok Thakur <alokthakur1987@gmail.com> wrote: > Dear, > > I am trying to provide you as much details as possible. > > answer` ( > `id` int(10) NOT NULL AUTO_INCREMENT, > `question_id` int(10) NOT NULL, > `user_id` int(10) NOT NULL, > `answer` int(10) NOT NULL, -> > `status` tinyint(1) NOT NULL, --> Status will be 0 or 1 means wrong > or right answer > `date` datetime NOT NULL, > PRIMARY KEY (`quiz_result_id`) > ) > > user` ( > `user_id` int(11) NOT NULL AUTO_INCREMENT, > `user` varchar(255) NOT NULL, > `username` varchar(255) NOT NULL, > `user_email` varchar(255) NOT NULL, > `user_gender` varchar(255) NOT NULL, > `refrence` varchar(255) NOT NULL, > `join_date` varchar(255) NOT NULL, > `status` tinyint(1) NOT NULL, > `banned` tinyint(1) NOT NULL, > PRIMARY KEY (`user_id`) > ) That's MySQL (i guess), please join a mysql-list. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Jan 17, 2012, at 8:35, Andreas Kretschmer <akretschmer@spamfence.net> wrote: > Alok Thakur <alokthakur1987@gmail.com> wrote: > >> Dear, >> >> I am trying to provide you as much details as possible. >> >> answer` ( >> `id` int(10) NOT NULL AUTO_INCREMENT, >> `question_id` int(10) NOT NULL, >> `user_id` int(10) NOT NULL, >> `answer` int(10) NOT NULL, -> >> `status` tinyint(1) NOT NULL, --> Status will be 0 or 1 means wrong >> or right answer >> `date` datetime NOT NULL, >> PRIMARY KEY (`quiz_result_id`) >> ) >> >> user` ( >> `user_id` int(11) NOT NULL AUTO_INCREMENT, >> `user` varchar(255) NOT NULL, >> `username` varchar(255) NOT NULL, >> `user_email` varchar(255) NOT NULL, >> `user_gender` varchar(255) NOT NULL, >> `refrence` varchar(255) NOT NULL, >> `join_date` varchar(255) NOT NULL, >> `status` tinyint(1) NOT NULL, >> `banned` tinyint(1) NOT NULL, >> PRIMARY KEY (`user_id`) >> ) > > That's MySQL (i guess), please join a mysql-list. > > DB aside the query you are looking for is very simple SQL. The only real trick is using SUM(case when status = 1/0 then 1 else 0 end to obtain the proper counts. Any reference materials covering table joining and group by will give you the syntax and examples needed to write your query. The lack of response is because most people are not going to bother answering very simple queries that beginner referencematerials cover adequately. Plus, you didn't display any effort in attempting to solve the question yourself; youcan do this by showing and and asking what you did wrong as opposed to simply asking for an answer. David J.