CODEWITHSUNDEEP
mysqlsql
Raven
Raven

Reputation: 119

SQL query for incrementing values of row data through sequencing

I have row and column locations of several students. Assuming number of rows and columns are fixed (to 3x3), how can I have a query result listing all row and column combinations, with students mapped to the correct location?

For example given these students data:

Student  Row     Column
Paul     1       1
Chris    1       3
James    2       2
Dwayne   3       3

How to have a query output like this:

Student  Row  Column
Paul     1       1
NULL     1       2
Chris    1       3
NULL     2       1
James    2       2
NULL     2       3
NULL     3       1
NULL     3       2
Dwayne   3       3

Please help! Thank you very much in advance.

Upvotes: 2

Views: 65

Answers (2)

Juan Ruiz de Castilla
Juan Ruiz de Castilla

Reputation: 974

First of all, you need to know that Mysql haven't a implicit generator of N numbers of rows, like other RDBMS have, but you can emulate this using something like this:

http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_code

Take a look for study porpuse.

But for a first approach to resolve your problem, you can try this:

SELECT IFNULL((SELECT STUDENT FROM StudentSeatPlan B WHERE B.ROW =  TB.ROW_ AND B.COLUMN = TB.COLUMN_),'') AS STUDENT,
TB.ROW_,TB.COLUMN_
FROM (
SELECT 1 ROW_,1 COLUMN_ UNION ALL 
SELECT 1,2  UNION ALL 
SELECT 1,3  UNION ALL 
SELECT 2,1  UNION ALL 
SELECT 2,2  UNION ALL 
SELECT 2,3  UNION ALL
SELECT 3,1  UNION ALL 
SELECT 3,2  UNION ALL 
SELECT 3,3) TB

Whatever, it seems like you have a schema problem, something wrong it happens that you need generate data in this form in Mysql, maybe you prefered make it in your app if is the case.

Upvotes: 0

Roy Doron
Roy Doron

Reputation: 595

While using PHP, Try mysql_insert_id() for your Code.

See Example here: http://php.net/manual/en/function.mysql-insert-id.php

Good luck.

Upvotes: 1

Related Questions