How do I write a query to display the customer who claimed maximum amount from an ERD diagram?-CodePudding

This is what I wrote but I am not getting the correct answer:

select top 1 customer.first_name 
from customer, claims
where customer.id = claims.id
order by claims.amount_of_claim desc
GO

CodePudding user response：

You are joining claim.id and customer.id. Those will never match except by accident. The number on a claims document will never match the number on your insurance card or policy except by accident.

When you write a query, the table rows are matched based only on the query expressions, not any constraints between tables. This means that the following query will try to match unrelated things:

select top 1 customer.first_name 
from customer inner join claims
    on customer.id = claims.id
order by claims.amount_of_claim desc

The diagram shows that a Claim is related to a Policy which in turn is related to a customer:

Claims(customer_policy_id) -> Customer_Policy(customer_id) -> Customer

You'll have to join these 3 tables in your query

select top 1 cust.first_name 
from customer cust
    inner join customer_policy pol on cust.id=pol.customer_id
    inner join claims cl on pol.id=cl.customer_policy_id
order by cl.amount_of_claim desc

In a relational database relations are represented by tables, not foreign key constraints. A Foreign Key Constraint is used to ensure that the FK values stored in a table are valid.

On top of that, an ER diagram is not a database diagram. The entities and relations shown in an ER diagram are don't map directly to tables. For example, a many-to-many relation between eg Customer and Address in an ER diagram would have to be translated to a bridge table, CustomerAddresses(CustomerId,AddressId). After all, a table is a relation in relational theory, and CustomerAddresses defines the relation between Customer and Address.