Help on querry, Thanks in advance, it is not a hard one for you i'm sure

  • Thread starter Thread starter Marc Robillard
  • Start date Start date
M

Marc Robillard

Hi all,

Thanks in advance, it is not a hard one for you i'm sure

Table History
-------------
ID
Prev_status
Current_status
....
--------------


Table Status
------------
ID
Description
------------


I need a SQL statement that will return Description from the Prev_status AND
Current_status on the same row.


Thanks you very much for your help !

Marc
 
How are the two tables related? Based on what you've posted, it looks like a
one-to-one relationship, but I'm assuming you left something out.
 
OK.. am I to take it that the IDs fields are not related? And that the ID in
the table status is related to Prev_status and Current_status?
 
OK, that's what I thought.

All you need to do is add the status table twice in query builder. Link
prev_status to one and current_status to the other.
 
Hi Lance,

Yet If I do so, none of the History gets out.

here is the generated SQL.

SELECT Status.Desc, Status_1.Desc, History.ID
FROM Status AS Status_1 INNER JOIN (Status INNER JOIN History ON Status.id =
History.Previous_Status) ON Status_1.id = History.Current_Status;

I don't get it !

Marc
 
Hi Lance,

Yet If I do so, none of the History gets out.

here is the generated SQL.
Click to expand...

The INNER JOIN will require that there be records in *all three* sets of data
- Status, Status_1 and History. You may need a LEFT OUTER JOIN instead of the
INNER JOIN on one or more of these. For example, if there is no record with
data in Previous_Status, you'll see nothing at all!

SELECT Status.Desc, Status_1.Desc, History.ID
FROM Status AS Status_1 INNER JOIN (Status INNER JOIN History ON Status.id =
History.Previous_Status) ON Status_1.id = History.Current_Status;

John W. Vinson [MVP]
 
Back
Top