SQL Join Help

[joero4]

I have a SQL problem. I need to join 3 tables. I have a tables a users, with a user_id primary key. I have a table of locations which references the user table using the user_id. I have a table of samples which references the location table with a location_id key. It looks like as follows...

USERS
.
.. LOCATIONS
.
.. SAMPLES

I need to join these three tables, BUT I need to get the latest sample in the SAMPLE Table. This is my problem. I can join the three tables, but how can I get the latest sample for each location, it should look like...

USER_ID LOCATION_ID SAMPLE_DATE
1 1 2/2/04
1 2 3/2/04
2 4 2/2/04
2 7 5/2/04

As you can see you can repeat the user_id, but you can't repeat the location_id. I need the last sample taken for each location. If anyone can help me that would be great. Thank You.

 

[carp]

Try this:

SELECT u.user_id, l.location_id, max(s.sample_date)
FROM user_table u, location_table l, sample_table s
WHERE u.user_id = l.user_id
  AND l.location_id = s.location_id
GROUP BY u.user_id, l.location_id;

 

[joero4]

That is great, but the problem gets more complex. In the sample table there are samples with the sample date. Meaning a sample may have 5 samples associated with one date. There is a sample_no in the table and a sample_id. The sample_no is associated with the date and the sample_id is the individual samples under that sample_no. The unfortunate thing is that this is all in one table when it should be in two tables. I'm not sure if you know what I mean or if I am confusing you. Thanks for your help.

 

[HimanB]

Hi, Try this:

SQL> desc users1
 Name                            Null?    Type
 ------------------------------- -------- ----
 USER_ID                                  NUMBER(10)

SQL> desc locations
 Name                            Null?    Type
 ------------------------------- -------- ----
 LOC_ID                                   NUMBER(10)
 USER_ID                                  NUMBER(10)

SQL> desc samples
 Name                            Null?    Type
 ------------------------------- -------- ----
 LOC_ID                                   NUMBER(10)
 USER_ID                                  NUMBER(10)
 SAMPLE_NO                                NUMBER(10)
 SAMPLE_ID                                NUMBER(10)
 SAMPLE_DATE                              DATE

SQL> select * from samples;

   LOC_ID   USER_ID SAMPLE_NO SAMPLE_ID SAMPLE_DA
--------- --------- --------- --------- ---------
        1         1         1         1 01-JAN-04
        1         1         1         2 01-JAN-04
        1         1         1         1 02-JAN-04
        1         1         1         2 02-JAN-04

SQL> SELECT u.user_id, l.LOC_ID,SAMPLE_NO,MAX(SAMPLE_ID)
  2  FROM users1 u, locations l, samples s
  3  WHERE u.user_id = l.user_id
  4  AND l.loc_id = s.loc_id
  5  GROUP BY u.user_id, l.loc_id,SAMPLE_NO;

  USER_ID    LOC_ID SAMPLE_NO MAX(SAMPLE_ID)
--------- --------- --------- --------------
        1         1         1              2
        2         1         1              2

HTH
Regards
Himanshu