By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,340 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

How to retrieve two different data from same database and same table.

P: 6
Database name : bd1
Table Name : schedule
Fields : associate_name, Category, Aux, Time
Raj ABC Login, 9:00:00
Raj ABC Logout 6:00:00
i want the below output

associate_name, Category, Aux, Login Time, Logout Time
Raj ABC Login 9:00:00 6:00:00

Please help me out.. im new to this.

Raj.
Mar 3 '13 #1

✓ answered by Rabbit

Join the table to itself. You will need a ranking column also so that you can join to the next row.

Share this Question
Share on Google+
15 Replies


Rabbit
Expert Mod 10K+
P: 12,359
Join the table to itself. You will need a ranking column also so that you can join to the next row.
Mar 3 '13 #2

P: 56
first in a table make one column as a primary key and den use it in another table as a foreign key then it will b easy to retrieve data
Mar 3 '13 #3

Rabbit
Expert Mod 10K+
P: 12,359
@syed, that won't work. We are only talking about one table here, not two.
Mar 3 '13 #4

P: 6
HI guys Please help me out. i wanted to implement this ASAP in my company without ur help i won't be able to do this
Mar 4 '13 #5

P: 6
Hi, can you guys show me the SQl statement to retrieve the data in single table as i have shown above ?
Mar 4 '13 #6

Rabbit
Expert Mod 10K+
P: 12,359
This is not a code writing service but if you make an attempt and post your SQL along with a description of the problem with the code, we can help guide you to a solution.
Mar 4 '13 #7

P: 6
the problem i have mentioned above is not enough ? is that what you are saying. and i have not asked you guys to write a code for me. im just asking how can i get the two set of data in a single line. please go through the question clearly... its a request
Mar 4 '13 #8

P: 56
single table ... then might be possible try using procedure ...
with if statement ...
if this
begin
bla bla bla
end
else
begin
bla bla bla
end
--------
hope by this u ill get some gelp
Mar 4 '13 #9

Rabbit
Expert Mod 10K+
P: 12,359
The question you have asked is fine. The problem is that you have asked for code. In post number 6, you said:
can you guys show me the SQl statement to retrieve the data in single table as i have shown above
That is asking for code. We are not here to do your work for you. We will gladly guide you along as you attempt the code, but you have to make an initial attempt at it. I have already supplied the method you can use in post #2.
Mar 4 '13 #10

P: 6
Okay here is the code i have written :

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim  objRS001, objrs002
  3.  
  4. 'Defining connection string, specify database driver and location of the database
  5. sConnection = "DRIVER={MySQL ODBC 5.1 Driver};Server=localhost;Database=homeshop18;Uid=root;Pwd=cfa;"
  6. Set ObjConn = Server.CreateObject("ADODB.Connection")
  7. ObjConn.Open(sConnection)
  8.  
  9. SQLRAJU001 = "select Associate_name, Category, Date, Aux, Login_time from Schedule where Aux = 'Login' and Date >= '" & from_date & "' and Date <= '" & to_date &"' Group by Associate_name "
  10. SQLRAJU002 = "select Associate_name, Category, Date, Logout_time from Schedule where Aux = 'Logout' and Date >= '" & from_date & "' and Date <= '" & to_date &"' Group by Associate_name "
  11.  
  12. Set objRS001 = ObjConn.Execute(SQLRAJU001)
  13. Set objRS002 = ObjConn.Execute(SQLRAJU002)
  14.  
  15. %>
  16.  
  17. <p class="auto-style1">&nbsp;</p>
  18. <h2 class="auto-style13">Associate Attendence Report</h2>
  19.  
  20. <table cellspacing="1" class="auto-style2" style="width: 580px; float: left">
  21. <tr>
  22. <td class="auto-style15"><strong>Associate Name</strong></td>
  23. <td class="auto-style15"><strong>Category</strong></td>
  24. <td class="auto-style15"><strong>Date</strong></td>
  25. <td class="auto-style15"><strong>Aux</strong></td>
  26. <td class="auto-style15"><strong>Login Time</strong></td>
  27. <td class="auto-style15"><strong>Logout Time</strong></td>
  28. </tr>
  29. <%    
  30. do while not objrs001.eof
  31. %>
  32. <%
  33. do while not objrs002.eof
  34. %>
  35. <tr>
  36. <td class="auto-style14"><%=objrs001("associate_name")%></td>
  37. <td class="auto-style14"><%=objrs001("Category")%></td>
  38. <td class="auto-style14"><%=objrs001("Date")%></td>
  39. <td class="auto-style14"><%=objrs001("Aux")%></td>
  40. <td class="auto-style14"><%=objrs001("Login_Time")%></td>
  41. <td class="auto-style14"><%=objrs002("Logout_Time")%></td>
  42. </tr>
  43. <%
  44. objrs001.movenext
  45. Loop
  46. objrs002.movenext
  47. Loop
  48. %>
  49. <%
  50. objrs001.close
  51. objrs002.close
  52. objconn.close
  53. %>
  54. </table>
this is not working ...
Mar 5 '13 #11

Rabbit
Expert Mod 10K+
P: 12,359
I don't know where this ASP code came out of all of a sudden. But since we're in the MySQL forum, the solution should be in MySQL only. So let's work only on the query. You'll need to create a query using the method in post 2.
Mar 5 '13 #12

P: 56
easy way make one column as a unique identity with that unique you can retrieve the data what ever you want ..........
Mar 5 '13 #13

Rabbit
Expert Mod 10K+
P: 12,359
@syed, that won't work if there are ever gaps in the sequence. As would happen if they delete records. Having a unique id is a good idea. But they would still need to create a ranking column.
Mar 5 '13 #14

P: 6
Thanks Everyone for you valuable time.. i could not reach to any conclusion.but im stopping this here. Since this is no code writing service i cannot take anymore help. Please dont spent ur time in replying.
Mar 6 '13 #15

zmbd
Expert Mod 5K+
P: 5,397
I've taken the liberty of setting Rabbit's response as the best answer provided within the thread context based on OP's abandonment of the question and robustness of the solution offered.
Mar 10 '13 #16

Post your reply

Sign in to post your reply or Sign up for a free account.