Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Question Unanswered: help with a query

    Hi guys...
    I am a begginer in this tool...
    I have a table that contain information about printers, they are identified by the serial number... in the same table a I have information about each one by date, for example:
    serial--%toner--date1
    seiral2--%toner--date1
    .
    .
    .
    serial1--%toner--date2
    serial2--%toner--date2
    .
    .
    .
    serial1--%toner--date3
    serial2--%toner--dadte4

    So, I want a query that show me the toner percent by date in the next structure

    serial1--%toner(date1)--%toner(date2)---%toner(date3)...
    serial2--%toner(date1)--%toner(date2)---%toner(date3)...
    serial3--%toner(date1)--%toner(date2)---%toner(date3)...
    serial4--%toner(date1)--%toner(date2)---%toner(date3)...
    .
    .
    .

    And that later on I will can to see if there were changes, for example. Maybe one day the %toner is 20 and the next is 90 so I can say that there were a toner-change---


    I did the next...

    select noSerial
    , toner as '%date14'
    , (select toner
    from discovery
    where date='2009-03-20'
    and noSerial='serial1'
    ) as '%date20'
    from discovery
    where date ='2009-03-14'
    and noSerial='serial1'

    with this query I can get in the same answer:

    serial %toner14 %toner20
    serial1 20 30

    But I can do it only with one serial and I want with a lot of them !!!!!

    If I erase the "and noSerial='serial1'" to the subquery sql return me the message
    "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

    and of course if I erase the same statement to the query I can get all my list.


    Please, help me with it....
    Regards,

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    use a self join e.g.
    untested
    select a.noSerial
    , a.toner as '%date14'
    , b.toner as '%date20'
    from discovery a
    join discovery b
    on a.noSerial=b.noSerial
    where a.date ='2009-03-14'
    and b.date='2009-03-20'
    Last edited by pdreyer; 04-15-09 at 04:30.

  3. #3
    Join Date
    Apr 2009
    Posts
    2

    Thanks

    Thanks a lot !!!!

  4. #4
    Join Date
    Apr 2009
    Posts
    16
    along the same lines on queries... I have a web page that builds a query from text boxes:

    Select x, y, z
    from atable
    where a<webvariablea, and b>webvariableb and c =<webvariblec

    i would like to have it still do the query if webvaribleC was left blank, I don't know what it needs to look like to make the variable in the query Not Required

    thanks,
    RobSimons

Posting Permissions

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