Combine results from the same table

[Rally2002]

Hi!

BACKGROUND

I have a table containing user information on my web server. This is an example:

ID IP DATE USER
---------------------------------------------
1 123.123.123.123 2002/04/24 10:05 Peter
2 234.234.234.234 2002/04/23 09:15
3 321.321.321.321 2002/04/24 11:06 John
4 234.234.234.234 2002/04/10 15:15 Mark
5 234.234.234.234 2002/04/09 16:34 Bill

ID is a counter
IP is the address of the user's computer
DATE is the date/time when the user accessed the web server
USER is the name of the user

As you can see from my example, the field USER can be empty. If the user logs in with his name, USER will be updated accordingly, otherwise it will be empty.

THIS IS WHAT I WANT TO DO

On the webpage I want to present information about who has accessed the site. If the USER field in a specific record in the table is empty, the table should be searched for a previous record with the same IP and with the USER field present.

In other words, if the user has not logged in and USER hence is empty, look for a previous login from the *same* IP and use the USER field from that record.

In the example above, record 2, 4 and 5 has the same IP. The information from record 2 should be supplemented with the USER from record 5. The reason to choose 5 and not 4 is to use the *latest* known user of that IP.

THIS IS WHAT I HAVE TRIED

Idea 1: Create a "lookup table" which only contains associated IPs and USERs. Then I join the main table with the lookup table.

SQL:

SELECT t1.id,t1.date,t1.ip,t1.user,t2.user FROM counter t1 LEFT JOIN (SELECT DISTINCT ip,user FROM counter WHERE user<>'') AS t2 ON t1.ip=t2.ip ORDER BY id DESC

The problem with this approach is the the lookup table will contain *several* records with the same IP but different name. When joining with the master table, all combinations between the &quot;master record&quot; and the lookup entries will appear.

In order to get this approach to work, I need to make sure that the lookup table only contains *one* entry per IP. This entry should be the one with the most recent date.

ID IP DATE T1.USER T2.USER
---------------------------------------------------------
5 234.234.234.234 2002/04/09 16:34 Bill Bill
5 234.234.234.234 2002/04/09 16:34 Bill Mark
4 234.234.234.234 2002/04/10 15:15 Mark Mark
4 234.234.234.234 2002/04/10 15:15 Mark Bill
3 321.321.321.321 2002/04/24 11:06 John John
2 234.234.234.234 2002/04/23 09:15 Mark
2 234.234.234.234 2002/04/23 09:15 Bill
1 123.123.123.123 2002/04/24 10:05 Peter

T1.USER is the user from the master table, T2.USER is the lookup name.

The idea was to present the lookup name in those cases the master name was empty.


Ok, this explaination was perhaps a bit long. I hope somwone will be able to help me out!

Cheers,
Rally2002

 

[bperry]

I couldn't really tell if you wanted to continue with the lookup table approach or not.
Here's a query without a separate table that you may want to consider. If it finds a user NULL or empty, then it runs a subquery (on the same table) to find the most recent user for a record with the same ip.
----------------------------------
Select t1.[id],t1.[date],t1.ip,
(CASE
when t1.[user] != '' then t1.[user] else
(
select top 1 [user] from counter sq
where sq.ip = t1.ip
order by [date] desc
)
END) as username
from counter t1
order by [id]
----------------------------

This seems like it works with the test data on my system. Would you like to try it?

bperry

 

[Rally2002]

Thanks for healping me out! I'm rather a beginner when it comes to SQL so it was nice to learn something new. I didn't know it was possible to use such things as CASE.

I tried to test your solution using MS Access 2000 but got a syntax error (missing operator) in the following part:

(CASE
when t1.[user] != '' then t1.[user] else
(
select top 1 [user] from counter sq
where sq.ip = t1.ip
order by [date] desc
)
END)

Do you have any idea what's causing it?

Regards,
Rally2002

 

[Rally2002]

I did some more tests and came up with a working syntax in MS Access:

SELECT t1.id, t1.ip, t1.user, IIf(t1.user<>'','',(select top 1 user from counter sq where sq.ip = t1.ip ORDER BY id DESC))
FROM counter t1
ORDER BY id DESC

 

[Rally2002]

I was a bit hasty sending my previous posting. Here is the correct SQL-statement:

SELECT t1.id, t1.date, t1.ip, t1.user, IIf(t1.user<>'','',(SELECT top 1 t2.user FROM counter t2 WHERE t2.ip = t1.ip AND t2.user<>'' ORDER BY id DESC)) AS lookup FROM counter t1 ORDER BY id DESC&quot;

I forgot to exclude fields without a user in the lookup. Also, the resulting table will cantain both the master user field and the lookup user.

/Rally2002