create procedure dbo.uspGetProductsWithSubcatName as
select p.*, s.Name as SubCatName
from 
Production.Product p
join
Production.ProductSubcategory s
on s.ProductSubcategoryID = p.ProductSubcategoryID

go

exec dbo.uspGetProductsWithSubcatName
go

create procedure dbo.uspGetProductsWIthJustProductIDAndNameAndThatsAllThankYou as
select ProductID, Name
from Production.Product
go

select ProductId, Name
from openrowset('SQLOLEDB.1', 'Server=.;Trusted_Connection=Yes', 'dbo.uspGetProductsWithSubcatName')
go

select *
from openrowset('SQLOLEDB.1', 'Server=.;Trusted_Connection=Yes', 'sp_who2')
go

create view dbo.ProductsPlus as
select p.*, s.Name as SubCatName
from 
Production.Product p
join
Production.ProductSubcategory s
on s.ProductSubcategoryID = p.ProductSubcategoryID
go

select ProductID, Name
from dbo.ProductsPlus

/*

Reasons for joins:

1. Columns required
2. Eliminate rows
3. Duplicate rows
4. NULL Inclusion
*/

select s.Name as SubCatName
from 
Production.ProductSubcategory s
full join
Production.Product p
on s.ProductSubcategoryID = p.ProductSubcategoryID
group by s.Name
having s.Name is not null
go


alter view dbo.ProductsPlus as
select p.*, s.Name as SubCatName
from 
Production.Product p
left join
Production.ProductSubcategory s
on s.ProductSubcategoryID = p.ProductSubcategoryID
;
go

select ProductID, Name
from dbo.ProductsPlus
go

select *
from Production.Product
where 1=2

--A few examples of this, so there are multiple 'companies' in play
select top 0 *
into [AdventureWorks$Products]
from Production.Product


'select Name
from [' + @Company + '$Products]'


create view dbo.AllProducts
as
select cast('AdventureWorks' as varchar(100)) as theCompanyName, *
from [AdventureWorks$Products]
union all
select 'Contoso Bikes', *
from [Contoso Bikes$Products]
union all
select 'LobsterPot Solutions', *
from [LobsterPot Solutions$Products]


select *
from dbo.AllProducts
where theCompanyName in ( 'LobsterPot Solutions', 'AdventureWorks')


create function dbo.fn_GetProductCount(@subcat int) returns int as
begin
return (
select count(*)
from Production.Product
where ProductSubcategoryID = @subcat)
end

select *, dbo.fn_GetProductCount(ProductSubcategoryID)
from Production.ProductSubcategory s

select *
, 
(
select count(*)
from Production.Product p
where p.ProductSubcategoryID = s.ProductSubcategoryID)
from Production.ProductSubcategory s


alter function dbo.fn_GetProductsPlus(@subcat int) returns table as
return (
select p.*, s.Name as SubCatName
from 
Production.Product p
left join
Production.ProductSubcategory s
on s.ProductSubcategoryID = p.ProductSubcategoryID
where p.ProductSubcategoryID = @subcat
)


select Name, ProductID
from dbo.fn_GetProductsPlus(3)