Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: SQL, How to include recs....

    Need to find a way to do this. I have two tables, One with codes i.e. S,R,T and other with transactions looks like this:

    Transaction table
    Emp Trans Tot...
    X55677 S 8
    X55677 R 2
    C22887 S 4
    C22887 T 3
    F66889 S 9
    F66889 R 4
    F66889 T 3

    Code table
    S
    R
    T

    Not sure if the code table even helps. What I want to do is show all three transactions for each employee even if they don't have any (would be zero) like so...

    Emp Trans Tot...
    X55677 S 8
    X55677 R 2
    X55677 T 0
    C22887 S 4
    C22887 T 3
    C22887 R 0
    F66889 S 9
    F66889 R 4
    F66889 T 3

    How can I get to this. I'm sure there must be a solution already posted for something like this but I'm not sure what to search for.

    Thanks for your thoughts.

  2. #2
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    select b.Emp,b.Code,Case when Tot is null then 0 else Tot end from Trans a right join
    (Select distinct Emp,Code from Trans cross join Code ) b
    on a.Emp = b.Emp and a.Trans = b.Code order by b.Emp,b.Code

    --- Sample Data insert as below ----

    create table Trans(Emp varchar(10),Trans char(1),Tot tinyint)
    insert into Trans select 'X55677', 'S', 8
    insert into Trans select 'X55677', 'R', 2
    insert into Trans select 'C22887', 'S', 4
    insert into Trans select 'C22887', 'T', 3
    insert into Trans select 'F66889', 'S', 9
    insert into Trans select 'F66889', 'R', 4
    insert into Trans select 'F66889', 'T', 3

    create table Code (Code Char(1))
    insert into code select 'S'
    insert into code select 'R'
    insert into code select 'T'

  3. #3
    Join Date
    Jan 2003
    Posts
    67

    Thanks!

    Changed my tables/fields and it worked great!
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •