Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Posts
    5

    Question Unanswered: Help needed with query

    Hello all, I am in need of help with a query I am trying to make.

    For the sake of this example, I have two tables, one called staff, with the following fields:

    staffid, fname, lname, salary, position
    and one called staffSales with these fields

    staffid, salesfigure, bonus
    I would like to make a query which looks into the staffSales table, and finds records which have a salesfigure in excess of 10000.

    From here, I would like this to add the bonus field in staffSales, to the salary field in staff, and then return these results.

    If anyone could shed some light on whether this would be possible, I would be extremely grateful. Many Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you select data from a SQL database using the SELECT statement
    you can limit the the data returned by the SELECT statement by using a WHERE clause
    you can retrievve the data form more than one table using either a JOIN clause or a WHERE clause, of the two the JOIN is probably the better one to learn, although the WHERE clause can be easier to form for beginners.
    SQL can perform all manner of mathmatical and and string operations. it was designed to be legible to English speakers

    SELECT my, comma, separated, columns, from MyTable
    join AnotherTable on Anotherable.Column=MyTable.column
    where acolumn < aNumber

    OR

    SELECT my, comma, separated, columns from MyTable, AnotherTable
    where acolumn < aNumber and Anotherable.Column=MyTable.column

    SELECT ((columna * 1.05) + (columnb * 1.175)) as myNewValue from MyTable
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2010
    Posts
    5
    Hi healdem,

    That was very useful, thanks for the help. Can I ask one more question? When I run the query:

    SELECT ((columna * 1.05) + (columnb * 1.175)) as myNewValue from MyTable

    I am returned a really long repeated list of values(84 to be precise!) does this suggest I have something wrong with my tables?

    Thanks again

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what sql did you use to generate the 84 columns?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try:

    SELECT a.staffid, a.fname, a.lname, a.salary, a.position, b.staffid, b.salesfigure, b.bonus, (a.salary + b.bonus) AS result
    FROM staff a, staffSales b
    WHERE a.staffid = b.staffid
    AND b.salesfigure > 10000;

    Result is the calculation that you are looking for but I have included all the other fields that you might want. If you don't need all the fields just remove and keep the ones you need.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Apr 2010
    Posts
    5
    Thats really helpful, thanks guys

Posting Permissions

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