declare @oppname as varchar(50)
select @oppname = GETDATE()
select @oppname = 'richSqlTest '+ @oppname
select '1. new test opportunity name = ', @oppname 

insert into opportunity (name) select @oppname
select '2. made a new test opportunity'

declare @oppid as varchar(50)
select top 1 @oppid = opportunityid from opportunity where name = @oppname order by createdon desc
select '3. the new opp has this ID:', @oppid

insert into phonecall ([from],[to], subject, regardingobjectid, regardingobjectidtype)
select top 1 u.systemuserid, c.contactid, 'A test phonecall', @oppid, 'opportunity' from systemuser u, contact c
SELECT '4. made a new phonecall for that opp:', activityid [from],[to], subject, regardingobjectid, regardingobjectidtype FROM phonecall where regardingobjectid = @oppid

SELECT '5. inner join displays the from and to values', o.name, o.opportunityid, p.activityid, p.[from], p.[to]
FROM opportunity o
join phonecall p ON o.opportunityid = p.regardingobjectid
where o.opportunityid = @oppid

SELECT '6. LEFT join hides from and to values', o.name, o.opportunityid, p.activityid, p.[from], p.[to]
FROM opportunity o
LEFT join phonecall p ON o.opportunityid = p.regardingobjectid
where o.opportunityid = @oppid