CODEWITHSUNDEEP
sqlrazorwebmatrix
Gavin5511
Gavin5511

Reputation: 791

Using INNER JOIN when pulling info from two tables using WebMatrix/Razor

I have written the following code to pull some information out of my database

@{
var db = Database.Open("StayInFlorida");
var propertyinfo = "SELECT * FROM PropertyInfo WHERE PropertyID='@0'";
var qpropertyinfo = db.QuerySingle(propertyinfo);
var ownerinfo = "SELECT FirstName, LastName FROM OwnerInfo WHERE OwnerID='2'";
var qownerinfo = db.QuerySingle(ownerinfo); 
}
<!DOCTYPE html>

<html>
<head>

</head>
<body>
@qpropertyinfo.PropertyName
@qownerinfo.FirstName
@qownerinfo.LastName
</body>
</html>

In both tables, there is a column called 'OwnerID', and I want to be able to pull the OwnerID from the PropertyInfo table and then show the FirstName and LastName of that owner from the OwnerID table. How do I do this, do I need to use an INNER JOIN statement?

I want it to be dynamic, so that when I change to a different property, the Owner Info changes too.

Upvotes: 2

Views: 1146

Answers (1)

mickfold
mickfold

Reputation: 2003

The following query should do what you require:

SELECT OwnerInfo.FirstName, OwnerInfo.LastName 
FROM OwnerInfo
INNER JOIN PropertyInfo on PropertyInfo.OwnerID = OwnerInfo.OwnerID
WHERE PropertyInfo.PropertyID='@0'

You can then pass in the PropertyId as a parameter as you have done in your question.

Upvotes: 2

Related Questions