Filter data from intermediate table with AND link

Question

Hello community,

i'have following scenario:

Tables and Columns (Database Type: Microsoft Sql Server 2005):

Table: Entries

• EntryID
• ... other unimportant columns

Table: Attributes

• AttributeID

Table: EntryAttributes

• EntryID [Releation To: Entries->EntryID]
• AttributeID [Releation To: Attributes->AttributeID]

So, how can i select only the Entries how contains multiple attributes like the following SQL statement (The statement doesn't work with the AND link):

SELECT *
FROM  [Entries] AS [t0]
INNER JOIN [EntryAttributes] AS [t1] ON [t0].[EntryID] = [t1].[EntryID]
WHERE ([t1].[AttributeID] = 1) AND ([t1].[AttributeID] = 1)

How do i build the correct SQL statement? I'm sure, there's already a solution somewhere, but i don't know which keywords i should use to search for that problem.

I using 'Linq to Entites', so maybe there's a simple Linq expression for that.

Thanks for reading my post!

Best regards!

Answer 1

The solution for this scenario is to join for each [AttributeID] which i would select:

SELECT * 
FROM  [Entries] AS [t0] 
INNER JOIN [EntryAttributes] AS [t1] ON [t0].[EntryID] = [t1].[EntryID] 
INNER JOIN [EntryAttributes] AS [t2] ON [t0].[EntryID] = [t2].[EntryID] 
WHERE ([t1].[AttributeID] = 1) AND ([t2].[AttributeID] = 2)