Return to Question

fix formatting

Source Link

edited Jan 22, 2022 at 13:33

26.9k
24
58
74

Tbl_Contact -ContactID

Tbl_Opportunity -OpportunityID -totalAmount

Tbl_Contact_Opportunity -ContactID -OpportunityID

Tbl_Contact
-ContactID

Tbl_Opportunity
-OpportunityID
-totalAmount

Tbl_Contact_Opportunity
-ContactID
-OpportunityID

select * from Tbl_Contact inner join Tbl_Contact_Opportunity on Tbl_Contact.ContactID = Tbl_Contact_Opportunity.ContactID inner join Tbl_Opportunity on Tbl_Contact_Opportunity.OpportunityID = Tbl_Opportunity.Opportunity.ID where Tbl_Opportunity.totalAmount=0

select * from Tbl_Contact
inner join Tbl_Contact_Opportunity on Tbl_Contact.ContactID = Tbl_Contact_Opportunity.ContactID
inner join Tbl_Opportunity on Tbl_Contact_Opportunity.OpportunityID = Tbl_Opportunity.Opportunity.ID 
where Tbl_Opportunity.totalAmount=0

This query returns a list of all the Tbl_Contact.ContactIDsTbl_Contact.ContactIDs that have a Tbl_OpportunityTbl_Opportunity with a totalAmount=0totalAmount=0. But I don't want that.

What I want to is to write a sqlSQL query to list all the Tbl_Contact.ContactIDsTbl_Contact.ContactIDs that have all of their corresponding Tbl_OpportunitysTbl_Opportunitys with a totalAmount=0totalAmount=0. I don't know how to do this.

Tbl_Contact | ContactID | | --------- | | 1 | | 2 |

ContactID
1
2

Tbl_Opportunity | OpportunityID | totalAmout | | ------------- | ---------- | | 1 | 321 | | 2 | 0 | | 3 | 0 | | 4 | 0 |

OpportunityID	totalAmout
1	321
2	0
3	0
4	0

Tbl_Contact_Opportunity | ContactID | OpportunityID | | --------- | ------------- | | 1 | 1 | | 1 | 2 | | 2 | 3 | | 2 | 4 |

ContactID	OpportunityID
1	1
1	2
2	3
2	4

but the result I really want is: | ContactID | | --------- | | 2 |

ContactID
2

That is because both of the Tbl_OpportunitysTbl_Opportunitys associated with ContactID=2ContactID=2 have the totalAmount=0totalAmount=0. Where ContactID=1ContactID=1 doesn't have both Tbl_OpportunitysTbl_Opportunitys associated with it having value totalAmount=0totalAmount=0. One of them is 123, so I don't want that included in the query result.

Can someone help me rewrite my query to make this work? I appreciate your time and help in advance.

Tbl_Contact -ContactID

Tbl_Opportunity -OpportunityID -totalAmount

Tbl_Contact_Opportunity -ContactID -OpportunityID

This query returns a list of all the Tbl_Contact.ContactIDs that have a Tbl_Opportunity with a totalAmount=0. But I don't want that.

What I want to is to write a sql query to list all the Tbl_Contact.ContactIDs that have all of their corresponding Tbl_Opportunitys with a totalAmount=0. I don't know how to do this.

Tbl_Contact | ContactID | | --------- | | 1 | | 2 |

Tbl_Opportunity | OpportunityID | totalAmout | | ------------- | ---------- | | 1 | 321 | | 2 | 0 | | 3 | 0 | | 4 | 0 |

Tbl_Contact_Opportunity | ContactID | OpportunityID | | --------- | ------------- | | 1 | 1 | | 1 | 2 | | 2 | 3 | | 2 | 4 |

but the result I really want is: | ContactID | | --------- | | 2 |

That is because both of the Tbl_Opportunitys associated with ContactID=2 have the totalAmount=0. Where ContactID=1 doesn't have both Tbl_Opportunitys associated with it having value totalAmount=0. One of them is 123, so I don't want that included in the query result.

Can someone help me rewrite my query to make this work? I appreciate your time and help in advance.

Tbl_Contact
-ContactID

Tbl_Opportunity
-OpportunityID
-totalAmount

Tbl_Contact_Opportunity
-ContactID
-OpportunityID

select * from Tbl_Contact
inner join Tbl_Contact_Opportunity on Tbl_Contact.ContactID = Tbl_Contact_Opportunity.ContactID
inner join Tbl_Opportunity on Tbl_Contact_Opportunity.OpportunityID = Tbl_Opportunity.Opportunity.ID 
where Tbl_Opportunity.totalAmount=0

This query returns a list of all the Tbl_Contact.ContactIDs that have a Tbl_Opportunity with a totalAmount=0. But I don't want that.

What I want to is to write a SQL query to list all the Tbl_Contact.ContactIDs that have all of their corresponding Tbl_Opportunitys with a totalAmount=0. I don't know how to do this.

Tbl_Contact

ContactID
1
2

Tbl_Opportunity

OpportunityID	totalAmout
1	321
2	0
3	0
4	0

Tbl_Contact_Opportunity

ContactID	OpportunityID
1	1
1	2
2	3
2	4

but the result I really want is:

ContactID
2

That is because both of the Tbl_Opportunitys associated with ContactID=2 have the totalAmount=0. Where ContactID=1 doesn't have both Tbl_Opportunitys associated with it having value totalAmount=0. One of them is 123, so I don't want that included in the query result.

Can someone help me rewrite my query to make this work?

Source Link

asked Jan 21, 2022 at 23:45

Jkay

SQL Query to remove associated records that all don't meet certain condition

I have the following tables and values in a 2014 SQL Express Server. They have been simplified to focus directly on the issue I'm having and for brevity.

Tbl_Contact -ContactID

Tbl_Opportunity -OpportunityID -totalAmount

Tbl_Contact_Opportunity -ContactID -OpportunityID

This is the query I started with:

This query returns a list of all the Tbl_Contact.ContactIDs that have a Tbl_Opportunity with a totalAmount=0. But I don't want that.

What I want to is to write a sql query to list all the Tbl_Contact.ContactIDs that have all of their corresponding Tbl_Opportunitys with a totalAmount=0. I don't know how to do this.

For example take the following tables:

Tbl_Contact | ContactID | | --------- | | 1 | | 2 |

Tbl_Opportunity | OpportunityID | totalAmout | | ------------- | ---------- | | 1 | 321 | | 2 | 0 | | 3 | 0 | | 4 | 0 |

Tbl_Contact_Opportunity | ContactID | OpportunityID | | --------- | ------------- | | 1 | 1 | | 1 | 2 | | 2 | 3 | | 2 | 4 |

with the query I wrote above the result would be:

ContactID
1
2

but the result I really want is: | ContactID | | --------- | | 2 |

Can someone help me rewrite my query to make this work? I appreciate your time and help in advance.

sql-server query