# Complicated Inner Join

**URL:** <https://forum.inductiveautomation.com/t/complicated-inner-join/7500>\
**Category:** Ignition\
**Created:** [October 30, 2013, 9:14pm UTC](https://forum.inductiveautomation.com/t/complicated-inner-join/7500 "2013-10-30T21:14:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![disav](https://avatars.discourse-cdn.com/v4/letter/d/8edcca/32.png) [@disav](https://forum.inductiveautomation.com/u/disav)\
**Post date:** [October 30, 2013, 9:14pm UTC](https://forum.inductiveautomation.com/t/complicated-inner-join/7500/1 "2013-10-30T21:14:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![drewdin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/drewdin/32/3363_2.png) [@drewdin](https://forum.inductiveautomation.com/u/drewdin)\
**Post date:** [October 30, 2013, 11:13pm UTC](https://forum.inductiveautomation.com/t/complicated-inner-join/7500/2 "2013-10-30T23:13:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![george](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/george/32/14066_2.png) [@george](https://forum.inductiveautomation.com/u/george)\
**Post date:** [October 30, 2013, 11:56pm UTC](https://forum.inductiveautomation.com/t/complicated-inner-join/7500/3 "2013-10-30T23:56:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![disav](https://avatars.discourse-cdn.com/v4/letter/d/8edcca/32.png) [@disav](https://forum.inductiveautomation.com/u/disav)\
**Post date:** [October 31, 2013, 12:58pm UTC](https://forum.inductiveautomation.com/t/complicated-inner-join/7500/4 "2013-10-31T12:58:53Z")

</div>

[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

---

<div class="post-metadata">

**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\
**Post date:** [October 31, 2013, 5:19pm UTC](https://forum.inductiveautomation.com/t/complicated-inner-join/7500/5 "2013-10-31T17:19:05Z")

</div>

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

```auto
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
```