How to get data from three table without using joins and subquery in sql

Question

I have three table

emp(id, name)
product(id, productname)
sales(id,emp_id,product_id,saleprice)

1. List all the employee with total sales.
2. Fetch the employee with highest sales.

Note: I dont want to use joining and subquery, please suggest me better way.

Answer 1

If you could refer to emp_id only then there is no need to use JOIN at all:

SELECT emp_id, SUM(salesprice) AS total_sales
FROM sales
GROUP BY emp_id
ORDER BY total_sales DESC  -- comment 2 lines to get answer for 1 question
LIMIT 1; -- MySQL version for SQL Server use `SELECT TOP 1`

Please note that if specific employee does not have any sales then it will be skipped. You won't get 0/NULL value for non existing data.

---

EDIT:

If you decide to use JOIN then:

SELECT e.id, e.name, SUM(s.salesprice) AS total_sales
FROM emp e
LEFT JOIN sales s
  ON e.id = s.emp_id
GROUP BY e.id, e.name
ORDER BY BY total_sales DESC LIMIT 1;

Answer 2

I think you can do like this. But better way is use join statement.But as your requirement you can do like this. I think this is this easiest way and very simple query.

I have provided create table and sample data insert query.

This is answer for your requirement

-- 1.List all the employee with total sales.
select distinct E.Id,E.Name,P.Productname,S.saleprice from Sales S,Emp E,Product P
where E.Id=S.Emp_id and P.Id=S.Product_id

See result for 1st question here

-- 2.Fetch the employee with highest sales.
select S.Emp_id,E.Name,SUM(S.saleprice) AS TotalSalePrice from Sales S,Emp E
where E.Id=S.Emp_id 
Group By S.Emp_id,E.Name

See result for 2nd question here

This is create table and sample data insert querys which create for your requirement

create table Emp
(
 [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
 [Name] [nvarchar](100)
 );

create table Product(
 [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
 [Productname] [nvarchar](100)
 );

create table Sales(
 [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
 [Emp_id] [int] FOREIGN KEY REFERENCES Emp([Id]) ,
 [Product_id] [int] FOREIGN KEY REFERENCES Product([Id]) ,
 [saleprice] [int] NOT NULL 
 );

INSERT INTO [dbo].[Emp] ([Name]) VALUES('Jone Doe')
INSERT INTO [dbo].[Emp] ([Name]) VALUES('Micheal Oshea')
INSERT INTO [dbo].[Emp] ([Name]) VALUES('Ish Thalagala')
INSERT INTO [dbo].[Emp] ([Name]) VALUES('Mark Poull')
INSERT INTO [dbo].[Emp] ([Name]) VALUES('Janne Marker')

INSERT INTO [dbo].[Product]([Productname]) VALUES ('Coca Cola')
INSERT INTO [dbo].[Product]([Productname]) VALUES ('Pepsi')
INSERT INTO [dbo].[Product]([Productname]) VALUES ('Tooth Brush')
INSERT INTO [dbo].[Product]([Productname]) VALUES ('Water Filter')
INSERT INTO [dbo].[Product]([Productname]) VALUES ('Playstation 4 pro')

INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(1,1,10)
INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(1,4,500)

INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(2,2,10)
INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(2,5,600)
INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(2,4,500)

INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(3,1,10)
INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(3,2,10)
INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(3,3,30)
INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(3,4,500)
INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(3,5,600)

INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(4,1,10)

INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(5,4,500)
INSERT INTO [dbo].[Sales]([Emp_id],[Product_id],[saleprice])VALUES(5,5,600)