Professional Web Applications Themes

Help with subquery - Dreamweaver AppDev

Hi all I am developing a barter site using php/mysql and dreamweaver mx2004. I have a transactions table with the following attributes: transactionid, transactiondate, insertdate, creditor, debitor, service, amount, chequeno. I have managed to create a query to retrieve the transactiondate, insertiondate, creditor, debitor, service, credit, debit, chequeno. This goes in my statement page: SELECT transactiondate, insertiondate, creditor, debitor, service, (amount * 1) 'credit', NULL 'debit', chequeno FROM `transaction` WHERE creditor = 'username' UNION SELECT t.transactiondate, t.insertiondate, t.creditor, t.debitor, t.service, NULL 'credit', (t.amount * -1) 'debit', t.chequeno FROM transaction t WHERE t.debitor = 'username' ORDER BY transactiondate ASC I have ...

  1. #1

    Default Help with subquery

    Hi all

    I am developing a barter site using php/mysql and dreamweaver mx2004.
    I have a transactions table with the following attributes:
    transactionid, transactiondate, insertdate, creditor, debitor, service,
    amount, chequeno.

    I have managed to create a query to retrieve the transactiondate,
    insertiondate, creditor, debitor, service, credit, debit, chequeno. This goes
    in my statement page:

    SELECT transactiondate, insertiondate, creditor, debitor, service, (amount *
    1) 'credit', NULL 'debit', chequeno
    FROM `transaction`
    WHERE creditor = 'username'
    UNION
    SELECT t.transactiondate, t.insertiondate, t.creditor, t.debitor, t.service,
    NULL 'credit', (t.amount * -1) 'debit', t.chequeno FROM transaction t WHERE
    t.debitor = 'username'
    ORDER BY transactiondate ASC

    I have a second query to calculate a figure for current balance, which is
    adding the sum of the amounts of a creditor with the sum of the amounts of the
    same person as a debitor (I hope that makes sense!) but I am getting a 1248
    Every derived table must have its own Alias error..

    This is the query:
    SELECT SUM(credit + debit)
    FROM (SELECT (t1.amount) 'credit', NULL 'debit' FROM transaction t1
    WHERE t1.creditor = 'username'
    UNION
    SELECT NULL 'credit', (t2.amount * -1) 'debit'
    FROM transaction t2
    WHERE t2.debitor = 'username')

    What am I doing wrong?
    Any help would be much appreciated.

    Thanks in advance
    Nalini

    Nalini Guest

  2. #2

    Default Re: Help with subquery

    I'm not sure about your table structure... but to answer your question it looks
    like you need an alias just after you close your subquery.

    SELECT SUM(credit + debit)
    .......
    FROM transaction t2
    WHERE t2.debitor = 'username') theAliasHere

    You might also want consider using CASE statements instead of UNION here.





    --- not tested with PHP
    SELECT transactiondate, insertiondate, creditor, debitor, service,
    CASE creditor
    WHEN 'username' THEN (amount * 1)
    ELSE NULL
    END 'credit',
    CASE debitor
    WHEN 'username' THEN (amount * -1)
    ELSE NULL
    END 'debit',
    chequeno
    FROM `transaction`
    WHERE creditor = 'username' OR
    debitor = 'username'


    --- not sure "amount * 1" is really neccessary
    SELECT SUM(
    (CASE creditor
    WHEN 'username' THEN (amount * 1)
    ELSE 0.0
    END) +
    (CASE debitor
    WHEN 'username' THEN (amount * -1)
    ELSE 0.0 END
    )
    ) 'currentBalance'
    FROM `transaction`
    WHERE creditor = 'username' OR
    debitor = 'username'

    mxstu Guest

  3. #3

    Default Re: Help with subquery

    mxstu,

    Thank you so much for replying.
    I am v new to sql and I hadn't heard of case statements before - am reading up
    on them now.
    Your queries worked. I am v grateful.

    You mentioned in your post that you were unsure about my table structure - for
    learning purposes could you tell me what is wrong with it?

    Nalini

    Nalini Guest

  4. #4

    Default Re: Help with subquery

    Well, you know your application best, but a better structure might begin with
    ....

    1) It is not a good idea to use a text value ('username') as a foreign key.
    Text values can change, causing inconsistencies in your data. Instead use a
    numeric record id. For example, 'username' probably comes from a table called
    'users' which probably has a numeric record id like 'userID'. Also, queries on
    numeric columns are typically faster than searches on a "varchar" columns.

    2) I would probably use a single column to store the transaction userID,
    instead of separate columns: 'creditor' and 'debitor'. You could still use
    CASE statements to generate separate columns in your resultsets.

    3) If needed, you could add a separate column to your table indicating the
    transaction type (ex. TransactionType char(1) and contain either 'C' for
    credit or 'D' for debit).

    4) I would probably store the transaction amount as a positive or negative
    number, depending on the transaction type, instead of multiplying debits by -1
    in the queries.


    mxstu Guest

Similar Threads

  1. Best way to use subquery?
    By greg.scharlemann@gmail.com in forum MySQL
    Replies: 4
    Last Post: November 27th, 01:25 AM
  2. Subquery
    By Ruszaj in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 2nd, 04:50 PM
  3. using list in subquery
    By spacehog in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: March 9th, 11:23 PM
  4. How use Subquery better ???
    By lubiel in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 07:47 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139