Query

[Bell1991]

I have two tables:

TableTransaction
NameFrom -> references TableName(NameID)
NameTo -> references TableName(NameID)
ActionID -> references TableAction (ActionID)

TableAction
ActionID
ActionName

TableName
NameID
Name

I need to get all columns in TableTransaction where
NameFrom = Name and ActionID = 1 or 2 or 3 or 4
and all columns from TableTransaction where
NameTo = Name and ActionID = 5

 

[dbomrrsm]

think a better explanation of your table structure is needed - confusing the life out of me anyhow

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[RiazKhanmohamed]

You can't link both nameFrom and nameTo at the same time otherwise you end up with a circular reference which will probably fail. try this:

select t.NameFrom, t.NameTo, t.ActionID
from TableTransaction t, TableName n
where
(t.NameFrom = n.NameID
and
(t.ActionID = 1
or t.ActionID = 2
or t.ActionID = 3
or t.ActionID = 4)
and n.Name = "expected name")
and
(t.NameTo = n.NameID
and
n.Name = "expected name"
and
t.ActionID = 5)


you don't need TableAction as the id is already in TableTransaction. I would suggest trying the where clause separately to make sure it is right, i'm a little rusty! Once you can get them separately, encase them in brackets and put an 'and' between them

 

[JamesLean]

I think these should give you what you want:

SELECT t.*
FROM tabletransaction t JOIN tablename n ON t.namefrom = n.nameid
WHERE n.name = 'joe bloggs'
  AND t.actionid BETWEEN 1 AND 4

SELECT t.*
FROM tabletransaction t JOIN tablename n ON t.nameto = n.nameid
WHERE n.name = 'joe bloggs'
  AND t.actionid = 5

--James