Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Investigating purchasing.productvendor and purchasing.vendor, join examples, create table example Postgres
-- Investigating purchasing.productvendor and purchasing.vendor
\d purchasing.productvendor
\d purchasing.vendor
-- How many records are there in each table?
-- How many different businessentityid?
select
count(*) as num_records,
count(distinct businessentityid) as num_businessentityid
from purchasing.productvendor;
select
count(*) as num_records,
count(distinct businessentityid) as num_businessentityid
from purchasing.vendor;
-- How many businessentityid are there matching between
-- purchasing.productvendor and purchasing.vendor?
-- How many records are there when matching on businessentityid?
select
count(distinct x.businessentityid) as num_businessentityid,
count(*) as num_records
from purchasing.productvendor x
join purchasing.vendor y
on x.businessentityid = y.businessentityid;
-- How many records are there in purchasing.productvendor which have no match
-- in purchasing.vendor when joining on businessentityid?
-- Hint: (you should be able to guess the correct answer to this
-- by looking at the results from the last few queries)
select count(*) as num_records
from purchasing.productvendor x
left join purchasing.vendor y
on x.businessentityid = y.businessentityid
where y.businessentityid is null;
-- How many records are there in product purchasing.vendor which have no match
-- in purchasing.productvendor when joining on businessentityid
-- Hint: (you should be able to guess the correct answer to this
-- by looking at the results from the last few queries)
-- Note both of the following queries achieve the same result
select count(*) as num_records
from purchasing.productvendor x
right join purchasing.vendor y
on x.businessentityid = y.businessentityid
where x.businessentityid is null;
select count(*) as num_records
from purchasing.vendor x
left join purchasing.productvendor y
on x.businessentityid = y.businessentityid
where y.businessentityid is null;
-- Make a table which has EVERY combination of businessentityid
-- (from the purchasing.vendor table)
-- and productid
-- (from the purchasing.productvendor table)
-- How many records does this table have?
-- Note that this has no practical here, other than to demonstrate a cross join
-- and how to create a table
create table cartesian_join_table as
select x.*, y.*
from (
select distinct businessentityid
from purchasing.vendor
) x
cross join (
select distinct productid
from purchasing.productvendor
) y;
select count(*)
from cartesian_join_table;
-- Remove the table from the database
drop table cartesian_join_table;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment