sql query

[akshita]

hi friends,

plz tell me how to write sql query for this

i ma having one table "object" in this table i am having 3 fields
1.name
2.specification
3.quantity

now i want that distinct name should appear for eg if there exists two entries for cd writer and each of having different specification then it shows only one time name and show both her specification,quantity

like

cd writer
specification :: hp
quantity: 5

specification:: samsung
quantity: 3



regards
akshita

 

[CasperTFG]

It will need to show the name twice if you want the different specifications. So that the recordset retrieved might look like:
[tt]
Name Specification Quantity
=============== =============== ===============
CDRW HP 5
CDRW Samsung 3
CDRW Creative 4
CD 50X HP 30
CD 50X Hitachi 100
CD 50X Creative 90
[/tt]
If you ask it to not show the Name for the duplicte then it may come out like this, and you would not know what it matches to.
[tt]
Name Specification Quantity
=============== =============== ===============
CDRW HP 5
CD 50X HP 30
Hitachi 100
Creative 4
Creative 90
Samsung 3
[/tt]
What I think is your concern is the reporting part. This can be done by setting a group by on the name:
[tt]
SELECT name, specification, quantity FROM object GROUP BY name;
Name Specification Quantity
=============== =============== ===============
CDRW HP 5
Samsung 3
Creative 4
CD 50X HP 30
Hitachi 100
Creative 90
[/tt]
Then you will have the records come out like this, but in your reporting part you can report based on group. And display the report with a Header of each group on Name. This all depends on how you are creating your report though. If you let us know that we may be able to help on that part.

Craig, mailto:sander@cogeco.ca

"Procrastination is the art of keeping up with yesterday."

I hope my post was helpful!!!

 

[akshita]

hi,

yes, u r right i am concerned with the reporting part.i want the report in the second format.
i used the sql query

select name,specification,quantity from object group by name

but it gives no results.

akshita

 

[vijayKumartaduri]

hai ,
Akshita first tell me where u want to show this info to user,if u want show this in report you can directly set this output in desgine time only.
....
if use this query
"select name,specification,quantity from object
group by name"
it will give u error saying that specification,quantity are not grouped functions.
.....
"select distinct name,specification, quantity
from object"
use with query only you can restict the records programetically

i written this bcoz your question is little bit confusion.
if u think this is not useful to leave it or else give me some more details.

see you

 

[akshita]

hi,

i want to show the result of this query in the report
i have to write this query in data environment

Name Specification Quantity
=============== =============== ===============
CDRW HP 5
Samsung 3
Creative 4
CD 50X HP 30
Hitachi 100
Creative 90




akshita