MySQL query to get next record if exists else 0

Question

I have the following table structure in MySQL for which I have to write code

 id  Docid  deptid 
-------------------
 1   Doc1  dept1
 2   Doc2  dept1
 3   Doc3  dept2
 4   Doc4  dept2
 5   Doc5  dept2

I have to write a query to get Docid in a sequential manner for the above MySQL structure in such a way that when client approach dept1, which has 2 Docids – Doc1 and Doc2. When a client approach dept1 for the first time it should direct him to Doc1 first and then to Doc2, and if he again wants to approach dept1 for more Docs then code again redirect it to Doc1 and vice versa.

The same should happen for dept2, which has 3 Docids – Doc3, Doc4 and Doc5. If the client approaches dept2 for the first time, then MySQL should direct it to Doc3 first, then to Doc4 and last to Doc5. If the client has approached all the Docs in dept2, he should be directed again to Doc3 and the whole cycle goes again whenever client approaches dept2.

I am not getting how to write a query or restructure my table columns so that I can get next Docid each and every time when I execute select query by filtering department wise.

Please guide me to restructure and write MySQL query for this case.

Thank you for all your valuable suggestions.

I'm managing in another table to save doctor Ids in department wise for which user is visited and by using this, i'm trying to get next doctor Ids http://stackoverflow.com/questions/1446821/how-to-get-next-previous-record-in-mysql — Akanksha, Dec 13 '16 at 10:09
order by clause will give all the results i need a single row each and every time i execute a query in a sequential manner as described above. — Akanksha, Dec 13 '16 at 10:13

score 1 · Answer 1 · answered Dec 13 '16 at 12:29

You could generate a deptdocid column using row number simulation for example given this

drop table if exists t;
create table t (id int, Docid varchar(10), deptid varchar(10));
insert into t values 
( 1 ,  'Doc1',  'dept1'),
( 2 ,  'Doc2',  'dept1'),
( 3 ,  'Doc3',  'dept2'),
( 4 ,  'Doc4',  'dept2'),
( 5 ,  'Doc5',  'dept2'); 

ariaDB [sandbox]> select t.*,
    -> if(t.deptid <> @p , @rn:=1,@rn:=@rn+1) deptdocid,
    -> @p:=t.deptid
    -> from (select @rn:=0,@p:='') rn, t
    -> order by t.deptid, t.id;
+------+-------+--------+-----------+--------------+
| id   | Docid | deptid | deptdocid | @p:=t.deptid |
+------+-------+--------+-----------+--------------+
|    1 | Doc1  | dept1  |         1 | dept1        |
|    2 | Doc2  | dept1  |         2 | dept1        |
|    3 | Doc3  | dept2  |         1 | dept2        |
|    4 | Doc4  | dept2  |         2 | dept2        |
|    5 | Doc5  | dept2  |         3 | dept2        |
+------+-------+--------+-----------+--------------+
5 rows in set (0.00 sec)

and if we have

MariaDB [sandbox]> select * from drdept;
+------+------+--------+---------+
| id   | dr   | deptid | visitno |
+------+------+--------+---------+
|    1 | abc  | dept1  |       1 |
|    2 | abc  | dept2  |       3 |
|    3 | def  | dept1  |       2 |
+------+------+--------+---------+
3 rows in set (0.00 sec)

Then joining drdept to the deptdocid should give us what we want

MariaDB [sandbox]> select dr.dr,dr.deptid,dr.visitno,s.deptid,s.docid,s.deptdocid
    -> from drdept dr
    -> left join
    -> (
    -> select t.*,
    -> if(t.deptid <> @p , @rn:=1,@rn:=@rn+1) deptdocid,
    -> @p:=t.deptid
    -> from (select @rn:=0,@p:='') rn, t
    -> order by t.deptid, t.id
    -> ) s on s.deptid = dr.deptid and s.deptdocid = dr.visitno;
+------+--------+---------+--------+-------+-----------+
| dr   | deptid | visitno | deptid | docid | deptdocid |
+------+--------+---------+--------+-------+-----------+
| abc  | dept1  |       1 | dept1  | Doc1  |         1 |
| abc  | dept2  |       3 | dept2  | Doc5  |         3 |
| def  | dept1  |       2 | dept1  | Doc2  |         2 |
+------+--------+---------+--------+-------+-----------+
3 rows in set (0.00 sec)

Clearly you need to maintain the drdept table and the visitno but after that it's pretty straightforward.

MySQL query to get next record if exists else 0

1 Answers1