Thanks the given sql is working ...it gave me correct data ..but now the problem is that it is displaying same data multipile times.i.e
ingredient........lotids............d_amount.......d_unit
xyz...............5t6y
xyz................7yh8
xyz................98uj
uvw.................9jnu
instead of that i need to display it as
xyz...............5t6y
..................7yh8
...................98uj
uvw.................9jnu
You are going to have to control the grouping of the xyz data by either testing for changes in some control variable assigned that value, or using built-in's of whatever flavor of sql your are using.
For instance, if your using mySql you could use group_concat
something like:
select table.*, GROUP_CONCAT(concat('Lot:',lotids,'Amt:',d_amount,'unit:',d_unit),'||') as big_lots
from table
left outer join ....
where ....
group by ingredient
having (table.whateverfield like 'whatever')
that give you one row per ingredient with a field called big_lots aggregating all the lots into it, delimited by || which you have to parse it out of to display nicely etc.. Anyway, it's an idea for which there are lots of solutions..
Bob
Unsolved 
