Re: sql query problem

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