I have 3 tables..
- unique listing of all employees...
- unique listing of all dates where transactions took place
- detail listing of all transactions
The detail listing of all transactions has a reference to the employee to
whom the transaction belongs as well as the date of the transaction.
I need to come up with a list of employees who did NOT have a transaction
for each date.
My initial thought was to create joins where I would get a listing of each
date along with all employees... then those employees who did not have a
transaction on a given date would showup as a null... for example:
Employees:
pete
david
joe
Dates:
10/5
10/6
10/7
Transactions
10/5 pete shoes
10/6 pete belt
10/6 david shoes
10/7 pete hat
10/7 david pants
I want to join those 3 tables so that I get this outcome:
Date Employee Transaction
10/5 pete shoes
10/5 david <null>
10/5 joe <null>
10/6 pete belt
10/6 david shoes
10/6 joe <null>
10/7 pete hat
10/7 david pants
10/7 joe <null>
I've done this type of thing before... but for some reason it's not working
for me. The outcome is not displaying the <null> rows.. only those rows
which contain a transaction.
Thanks.First Cross join the Employee table and the Dates table to get the full rang
e
of possible values. Then outer join to the Transactions table to get
yourtransactions.
SELECT d.Dates, e.EmpName, t.Trans
FROM Dates d
CROSS JOIN Emp e
LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
(Obviously I made up column names and table names :P )
HTH,
John Scragg
"David Sampson" wrote:
> I have 3 tables..
> - unique listing of all employees...
> - unique listing of all dates where transactions took place
> - detail listing of all transactions
> The detail listing of all transactions has a reference to the employee to
> whom the transaction belongs as well as the date of the transaction.
>
> I need to come up with a list of employees who did NOT have a transaction
> for each date.
>
> My initial thought was to create joins where I would get a listing of each
> date along with all employees... then those employees who did not have a
> transaction on a given date would showup as a null... for example:
>
> Employees:
> pete
> david
> joe
> Dates:
> 10/5
> 10/6
> 10/7
> Transactions
> 10/5 pete shoes
> 10/6 pete belt
> 10/6 david shoes
> 10/7 pete hat
> 10/7 david pants
>
> I want to join those 3 tables so that I get this outcome:
> Date Employee Transaction
> 10/5 pete shoes
> 10/5 david <null>
> 10/5 joe <null>
> 10/6 pete belt
> 10/6 david shoes
> 10/6 joe <null>
> 10/7 pete hat
> 10/7 david pants
> 10/7 joe <null>
> I've done this type of thing before... but for some reason it's not workin
g
> for me. The outcome is not displaying the <null> rows.. only those rows
> which contain a transaction.
> Thanks.
>
>|||Please post the offending query. I bet there's something in it tha diminishe
s
the effects of outer joins - that is if you use them at all. Can't tell unti
l
we see the query. :)
ML|||I've never heard of a CROSS join... I'll use that and see what it does for
me.
thanks
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
> First Cross join the Employee table and the Dates table to get the full
> range
> of possible values. Then outer join to the Transactions table to get
> yourtransactions.
> SELECT d.Dates, e.EmpName, t.Trans
> FROM Dates d
> CROSS JOIN Emp e
> LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
> (Obviously I made up column names and table names :P )
> HTH,
> John Scragg
> "David Sampson" wrote:
>|||That is EXACTLY what I needed!!!!!
Thanks alot!!!!
David
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
> First Cross join the Employee table and the Dates table to get the full
> range
> of possible values. Then outer join to the Transactions table to get
> yourtransactions.
> SELECT d.Dates, e.EmpName, t.Trans
> FROM Dates d
> CROSS JOIN Emp e
> LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
> (Obviously I made up column names and table names :P )
> HTH,
> John Scragg
> "David Sampson" wrote:
>|||I'm not making it up :P
A CROSS join is the cartesian product of two tables. So if you have 3 rows
in each table. A CROSS join will give you 9 rows. Every possible
combination.
It is the same as saying
SELECT d.Dates, e.EmpName
FROM Dates d, Employees e
Notice that there is no where clause. Same as there is no "ON" clause in my
example posted earlier. I just prefer the clarity of describing my join
types over the method show above.
Best of luck,
John
"David Sampson" wrote:
> I've never heard of a CROSS join... I'll use that and see what it does for
> me.
> thanks
>
> "John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
> news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
>
>
sql
