SQL Join or LOAD Join

[#1]

Hi All,

Kind of confused with JOIN thing. Maybe someone can advise?

I have a table with 6 fields (contract, task, user, date, status, hours) . Firstly, I need to distinctivelly select all rows with minimal date, so it goes:

1. SQL SELECT contract, task, user, MIN (date) FROM table1

Now I need to add 2 more fields - I want to join my temp_table with table 1, so it will be ???

1. SQL SELECT contract, task, user, MIN (date) FROM table1

2. JOIN SELECT contract, task, user, date, status, hours FROM table1

Or am I reall confused with JOIN syntaxis?

Best regards,

Mikhail

[#2]

Allmost forgot - maybe I shall use JOIN LOAD function? Please, advise )

[#3]

I recommend you find the meaning of a join, with their respective roles, left, inner, and so on.
After this study can clearly resolve your concerns.

I recommend to catch a single database, so we recommend a one-time charge in the table, then you can do this with a call to load the resident, as often as required, then you can delete if not ultilizas with a drop table.
Good luck.
JCM

[Anonymous · Accepted solution]

Mikhail,
To get the right answer you have to identify the problem first. I don't thnk that your pupose is just to use join. My assumption (may be wrong) that you want to get the data from the table1 only for the earliest date for each user/task/contract combination.
If the assumption is correct, It may be something like this:

Table_src:
SQL SELECT
 contract, task, user, date, status, hours
FROM table1;
Table:
LOAD
 contract, task, user,
 MIN (date) as MinDate
RESIDENT Table_src
GROUP BY contract, task, user;
LEFT JOIN (Table) LOAD
 date as MinDate,
 contract, task, user, date, status, hours
RESIDENT Table_src;
Drop table Table_src;
Drop Field MinDate;

(There could be better ways, I think...)

[rwunderlich]

Does your DBMS support correlated subqueries? How about doing it al lin the SELECT like this:

SELECT contract, task, user, date, status, hours
FROM table1 X
WHERE date =
 (SELECT MIN(date)
 FROM table1
 WHERE contract = X.contract AND task = X.task)

-Rob

[#6]

Thanks, Michael. Your suggestion was totally right )