SQL teacher Help!

[johnvai]

Dear Friends:

I have this case and i don't know if there's a solution for it.
I have the following units table
ID NAME SUBUNIT CONVERSION
1 Kg 1 1
2 Ton 1 1000
3 Each 3 1
4 Pack*2pc 3 2
5 Box*8Pck 4 8

I want to get all units where have a link with 'Each' like:
Each-Pack*2pc-Box*8Pck
and if i choose Kg show me: Kg-Ton
in case I want to select the Pack*2pc show me: Pack*2pc-Box*8Pck

the search has to go from bottom to top.

Any SQL that does this?

Thanks in advance.
John Vai

 

[SkipVought]

John Vai,

Not very clear at all what you want. How do you propose to "link" these?

Please explain the BUSINESS CASE that you are trying to solve rather than the fields and links.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[lotharious]

If I understand this correctly you will not be able to do it with a single sql statement

However if you add another field say unit type, where KG/Ton would be type 1, and Each, Box, Pack would be type 2, then you can just select out of the table by the unit type and output the data for each successive row returned.

ID NAME SUBUNIT CONVERSION UNITTYPE
1 Kg 1 1 1
2 Ton 1 1000 1
3 Each 3 1 2
4 Pack*2pc 3 2 2
5 Box*8Pck 4 8 2

So if you want KG you would find the unit type for kg which is 1, then do something like this
Select [<tablename>].* from <tablename> where [tablename].[unittype]=1 order by ID

then you just output each line that is returned in the format that you need.