Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    17

    Thumbs up Unanswered: Help write query (was "Help Me Up")

    Please help to get query for result like this...

    MASTER TABLE

    BranchID Address
    JAKARTA JAKARTA - PUSAT
    MEDAN Jl. Medan Tj. Morawa km. 9,5 Medan 20229
    PADANG Jl. Raya By Pass No. 10 Kel. Kalumbu, Kec. Kuranji


    ProductID ProductCode
    CHDCD Handy Clean 30 ml/botol
    CKPXB Kalpanax Cream 5 gram/Tube
    CTMDA Trimadan Cream 10 gram/tube

    AccountID AccountDesc
    116 Lembur
    122 Transportasi
    126 Uang Makan
    211 Fotokopi, Stensil & Cetak


    TRANSACTION TABLE

    BranchID ProductID AccountID Total
    MEDAN CHDCD 116 500
    PADANG CKPXB 126 1000


    What Result I need is like this :

    BranchID ProductID AccountID Total
    JAKARTA CHDCD 116 0
    JAKARTA CHDCD 122 0
    JAKARTA CHDCD 126 0
    JAKARTA CHDCD 211 0
    JAKARTA CKPXB 116 0
    JAKARTA CKPXB 122 0
    JAKARTA CKPXB 126 0
    JAKARTA CKPXB 211 0
    JAKARTA CTMDA 116 0
    JAKARTA CTMDA 122 0
    JAKARTA CTMDA 126 0
    JAKARTA CTMDA 211 0
    MEDAN CHDCD 116 500
    MEDAN CHDCD 122 0
    MEDAN CHDCD 126 0
    MEDAN CHDCD 211 0
    MEDAN CKPXB 116 0
    MEDAN CKPXB 122 0
    MEDAN CKPXB 126 0
    MEDAN CKPXB 211 0
    MEDAN CTMDA 116 0
    MEDAN CTMDA 122 0
    MEDAN CTMDA 126 0
    MEDAN CTMDA 211 0
    PADANG CHDCD 116 0
    PADANG CHDCD 122 0
    PADANG CHDCD 126 0
    PADANG CHDCD 211 0
    PADANG CKPXB 116 0
    PADANG CKPXB 122 0
    PADANG CKPXB 126 1000
    PADANG CKPXB 211 0
    PADANG CTMDA 116 0
    PADANG CTMDA 122 0
    PADANG CTMDA 126 0
    PADANG CTMDA 211 0


    anyone could help please??

  2. #2
    Join Date
    Apr 2009
    Posts
    6
    Can you please tell whether Master Table is one single table with 6 columns or you are trying to mention 3 different tables.

    You can use inner join for the first 3 tables. it will generate all possible combination. Then you can use left join this table with the transaction table. You will have to use case statement to show 0 instead of NULL for those rows that does not match with transaction table.


    Just answer my first question, then I can show you a sample query.

  3. #3
    Join Date
    Feb 2009
    Posts
    17

    thanks maeenul

    it's mean 3 master table

  4. #4
    Join Date
    Apr 2009
    Posts
    6
    select A.BrachID, A.ProductID, A.AccountID, isnull(T.Total,0) as Total

    (
    select M1.BrachID, M2.ProductID, M3.AccountID
    from M1, M2, M3
    ) A left join
    Transaction as T
    on A.BranchID=T.BranchID
    and A.ProductID = T.ProductID
    and A.AccountID = T.AccountID



    I think this query should server your purpose.

Posting Permissions

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