Complicated Inner Join

Ignition
1

Hey ya’ll I currently have two databases; an equipment database and a log database.

I currently have a table reading equipment.ID, Log.Date, and the Log.String where the log.Index is 5.
Is there anyway to add another column to this table that pulls Log.By for an Log.Index of 6? Current code is below.

Select equipment.ID, Log.Date, Log.String
From equipment
inner join log
On equipment.ID = log.ID
where log.Index= ‘5’
Order BY equipment.ID

2

so you want both log.index 5 and 6?

Select equipment.ID, Log.Date, Log.String
From equipment
inner join log
On equipment.ID = log.ID
where log.Index= ‘5’ or log.Index= ‘6’
Order BY equipment.ID

3

That won’t work as log.Index can’t be both 5 and 6. Maybe you meant to write “or” instead of “and”?

However I don’t think that’s what’s being asked for? disav, can you clarify what you’re after?

You can join a table twice in a query, you need to give it two different aliases. But I’m not sure if that’s what you mean either.

Also what SQL database are you using? Most of those column names are keywords in MSSQL so you’d need to put square brackets round them in queries.

4

[quote=“george”]That won’t work as log.Index can’t be both 5 and 6. Maybe you meant to write “or” instead of “and”?

However I don’t think that’s what’s being asked for? disav, can you clarify what you’re after?

You can join a table twice in a query, you need to give it two different aliases. But I’m not sure if that’s what you mean either.

Also what SQL database are you using? Most of those column names are keywords in MSSQL so you’d need to put square brackets round them in queries.[/quote]

Yeah thats not exactly what I was attempting to do.

Basically I have an eqiupment database that contains equipment IDs and the equipments current status. The table currently displays all equipment that is currently down for repair along with the date is was taken out of operation and the reason for being taken out of operation (Index 5). What I would like to add to the table is add the person who completed the previous maintaince on the equipment, Log.by (Index 6)

I am utilizing MSSQL, the column names I listed above arent the actual column names. Thanks

5

You should try joining back to the same table again and then filtering on the other record you are looking for:

SELECT e.ID, log.Date, log.String, nextlog.by
FROM equipment e
  INNER JOIN log
    ON e.ID = log.ID
      AND log.Index = '5'
  INNER JOIN log nextlog
    ON e.ID = log.ID
      AND nextlog.Index = '6'
ORDER BY e.ID