CODEWITHSUNDEEP
phpmysql
Akanksha
Akanksha

Reputation: 59

MySQL query to get next record if exists else 0

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.

Upvotes: 1

Views: 96

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

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.

Upvotes: 1

Related Questions