Click here to Skip to main content
15,891,657 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have three tables of Northwind database , Employee(employeeID) 1-* Order(Orderid,customerid,employeeid)*-1 Customer(customerID) . 1 Employee has many Orders and 1 customer have many orders , so it is 1* *1 relationship , with an order table in the middle. So how do i extract and display in a mvc view ALL EMPLOYEES THAT HAVE CUSTOMERS ?

What I have tried:

i just explained the problem above
Posted
Updated 23-May-17 10:39am
Comments
Maciej Los 23-May-17 11:32am    
Ok, you explained, but what have you tried?

1 solution

Firstly, in my copy of the Northwind database, downloaded directly from MSDN, the tables are called Employees, Orders and Customers. It is important to be precise.

The tables can be queried using JOINs - this CodeProject article explains the various types of join Visual Representation of SQL Joins[^]

Because you are trying to limit the information returned - "all employees that have customers" implies "do not show employees that do not have customers", an INNER JOIN would be appropriate - refer to the article to see why.
You don't actually need to join across the three tables because if you look at the Create script for the table Orders you will see
SQL
...
[CustomerID] [nchar](5) NULL,
...
ALTER TABLE [dbo].[Orders]  WITH NOCHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
GO
(In SQL Server Management Studio, if you right-click on a table name in the Object Explorer window, select "Script table as" then "CREATE to" then "New Query Editor Window" you can get to the information pasted above).
That basically means that if you do include a value for CustomerID on the Orders table then it must exist as CustomerID on the Customers table, otherwise that column will contain NULL.

So, to just list the Employees details that appear on the Orders table:
SQL
Select E.LastName, E.FirstName, E.Title
FROM Orders O
INNER JOIN Employees E ON O.EmployeeID = E.EmployeeID
WHERE CustomerID IS NOT NULL
That happens to be exactly the same as including Customers on another INNER JOIN i.e.
SQL
Select E.LastName, E.FirstName, E.Title
FROM Orders O
INNER JOIN Employees E ON O.EmployeeID = E.EmployeeID
INNER JOIN Customers C ON O.CustomerID = C.CustomerID
Again, if you look at the images on that article it will become clear why.

Translating that to MVC, Linq or whatever else you are trying to do is left as an exercise for you, as you have not provided enough information (nor effort) for me to help you with that.
 
Share this answer
 
Comments
ddgjgj 23-May-17 17:36pm    
First, we assume the every Order has a Customer, so if an Employee has an Order, he must also have a Customer. So, we can forget about the Customer Table. We really just want to know, Which Employees have Orders. For that, we look at the Orders and gather a list of Employees that own them:

(from o in Orders
select o.Employee).Distinct()

So this is a part of how view looks like:

@model Northwind.Employees

@{
ViewBag.Title = "Me";
Layout = "~/Views/Shared/MasterDetailsLayoutPage.cshtml";
}








@Html.DisplayNameFor(model => model.EmployeeID)



@Html.DisplayFor(model => model.EmployeeID)

........AND SOME OTHER EMPLOYEES FIELD....
THEN HERE display a table:
foreach(item in Model.Customers)

.....display a table of customers for this employee(example EMPLOYEEID:5 ).



And this is the controller :

dbNorthwindEntities db = new dbNorthwindEntities();

// GET: Test
/* public ActionResult Details()
{
//query comes here
return View();
}*/


Can you correct me or help me with that now . I really really appriciate your explanation above , I was like 'WOW' !
CHill60 24-May-17 5:17am    
The query is going to be the more simple
Select E.LastName, E.FirstName, E.Title --,and some other employees fields
FROM Orders O
INNER JOIN Employees E ON O.EmployeeID = E.EmployeeID
... and that query needs to go into the Model, not the Controller.
To be fair, most of the examples on the internet seem to use Linq to Sql or Entity Framework, but I did find this simple example for you to follow c# - using simple queries in ASP.NET MVC - Stack Overflow[^] and there is also some recommended reading on this link - How can I connect MVC to sql server and use it like c# in add, edit, delete and select | The ASP.NET Forums[^]
I should explain now that MVC is not my area of expertise, so if you are still having problems it might be worth raising a new question...making sure that you include the code from your comment
ddgjgj 25-May-17 4:17am    
Thank you so much Mr.Genius ! I really really appriciate every response you wrote .

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900