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

[capri12s]

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.

[Rabbit]

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

[Syed Hadi]

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

[Rabbit]

@syed, that won't work. We are only talking about one table here, not two.

[capri12s]

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

[capri12s]

Hi, can you guys show me the SQl statement to retrieve the data in single table as i have shown above ?

[Rabbit]

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.

[capri12s]

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

[Syed Hadi]

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

[Rabbit]

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.

[capri12s]

Okay here is the code i have written :

<%
Dim  objRS001, objrs002

'Defining connection string, specify database driver and location of the database
sConnection = "DRIVER={MySQL ODBC 5.1 Driver};Server=localhost;Database=homeshop18;Uid=root;Pwd=cfa;"
Set ObjConn = Server.CreateObject("ADODB.Connection")
ObjConn.Open(sConnection)

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 "
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 "

Set objRS001 = ObjConn.Execute(SQLRAJU001)
Set objRS002 = ObjConn.Execute(SQLRAJU002)

%>

<p class="auto-style1">&nbsp;</p>
<h2 class="auto-style13">Associate Attendence Report</h2>

<table cellspacing="1" class="auto-style2" style="width: 580px; float: left">
<tr>
<td class="auto-style15"><strong>Associate Name</strong></td>
<td class="auto-style15"><strong>Category</strong></td>
<td class="auto-style15"><strong>Date</strong></td>
<td class="auto-style15"><strong>Aux</strong></td>
<td class="auto-style15"><strong>Login Time</strong></td>
<td class="auto-style15"><strong>Logout Time</strong></td>
</tr>
<%	
do while not objrs001.eof
%>
<%
do while not objrs002.eof
%>
<tr>
<td class="auto-style14"><%=objrs001("associate_name")%></td>
<td class="auto-style14"><%=objrs001("Category")%></td>
<td class="auto-style14"><%=objrs001("Date")%></td>
<td class="auto-style14"><%=objrs001("Aux")%></td>
<td class="auto-style14"><%=objrs001("Login_Time")%></td>
<td class="auto-style14"><%=objrs002("Logout_Time")%></td>
</tr>
<%
objrs001.movenext
Loop
objrs002.movenext
Loop
%>
<%
objrs001.close
objrs002.close
objconn.close
%>
</table>

this is not working ...

[Rabbit]

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.

[Syed Hadi]

easy way make one column as a unique identity with that unique you can retrieve the data what ever you want ..........

[Rabbit]

@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.

[capri12s]

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.