SQL If/Then Help

[MasterBIB]

Admitting out of the gate, I am a novice when it comes to SQL, so I am hoping someone can help me with this.

I am attempting to create a query that returns records based on a number of points of criteria.

I have two tables:

Table 1:
ElementID (PK)
ElementType

Table 2:
ValueID (PK)
Value Image (OLE object)
Lift (%)
Significant (yes/no)
Winner (yes/no)
Control (yes/no)
ElementID (FK)

Each element can have multiple values, so my tables have a 1 to many relationship. While each element can have multiple values, only 1 of those values can be considered to be the control (yes). Additionally, a control can never be considered significant.

I need my query to return ALL values that are significant (yes) + plus the control value that is associated with that element. But more importantly, if one of the elements does not have a significant value (no), do not show the corresponding control value. The problem I am having is the control value can never be considered significant, so the AND & the OR operators are not working because the query either returns nothing or everything.

Ex Table

ElementID | Element Type
1 | Header
2 | Button
3 | Text

ValueID | Value Image | Lift | Significant | Winner | Control | ElementID
1 | Image | 0.00% | No | No | Yes | 1
2 | Image | 5% | Yes | No | No | 1
3 | Image | 10% | Yes | Yes | No | 1
4 | Image | 0.00% | No | No | Yes | 3
5 | Image | 11% | No | Yes | No | 3
6 | Image | 1% | Yes | No | No | 3

 

[plog]

Sounds like a job for a sub-query. This sub-query will determine what Elements to show. From your definition you want just those that have a record where Significant='No'. This is your sub-query SQL:

SELECT ElementID FROM [Table 2] WHERE Significant='No' GROUP BY ElementID;

That sub query will determine which records gets shown. Now all you have to do is bump that sub query against your [Table 2]. This is that SQL (I named the above sub-query 'subNonSignficants'):

SELECT * 
FROM ([Table 2] INNER JOIN subNonSignificants ON [Table 2].ElementID = [subNonSignificants].ElementID));

That should give you want you want. If not, please post what results should be returned based on the sample data you have already posted.

 

[MasterBIB]

Thank you! This worked