Skip site navigation (1) Skip section navigation (2)

Re: sql query problem

From: Alok Thakur <alokthakur1987(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: sql query problem
Date: 2012-01-16 09:48:19
Message-ID: 8682062e-b8fa-4778-a544-ab7671ff721b@c1g2000pbf.googlegroups.com (view raw or flat)
Thread:
Lists: 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(dot)si(dot)(dot)(dot)(at)gmail(dot)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-(dot)(dot)(dot)(at)postgresql(dot)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-(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-sql


In response to

Responses

pgsql-sql by date

Next:From: Tim LandscheidtDate: 2012-01-16 15:12:26
Subject: Re: Token separation
Previous:From: John TuliaoDate: 2012-01-16 08:43:35
Subject: Re: Query Problem... Left OuterJoin / Tagging Issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group