problems with a query

Question

Post reply

problems with a query

celetier

SSC Rookie

Points: 26
More actions
April 13, 2009 at 6:35 pm

#99606

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)...
.
.
.
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 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,

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323753 More actions · Answer 1

the trick here is to join a table against a copy of itself...to do that, you need to alias one or both tables;

something like this is pretty close, use this as an example:

select

D1.noSerial,

D1.toner as '%date14'

D2.toner,

D2.date As '%date20'

FROM discovery D1 --D1 is an Alias for the table

Left Outer Join discovery D2 --D2 Alias allows us to join the same table agaisnt itself

ON D1.noSerial = D2.noSerial

and D1.date < D2.Date

where D2.date='2009-03-20'

and D1.date ='2009-03-14'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

celetier SSC Rookie Points: 26 More actions · Answer 2

Thanks a lot !!!!!!!!!!!!!!

but... how can I do if I like present 30 days and not only two?

and... if I like show the %toner even one day I don't have it and but I have the next day... in another words...

Example:

sarial %toner date

1 23 date1

2 89 date1

3 89 date1

5 50 date1

10 49 date1

2 39 date2

4 90 date2

10 89 date2

So, I want that the query show me...

serial %toner_date1 %toner_date2

1 23 -

2 89 39

3 89 -

4 - 90

5 50 -

10 49 89

it is possible it????