May 4, 2006 at 2:48 pm
Hi Everyone,
I'm wanting to display a report on a coldfusion page that shows products and there parts. The products and the parts of the products are stored in different tables.
Here are the tables I have:
Parent Table:
id, productname, itemnumber, amount, qty
Child Table:
id, mainitemid, productname, amount
The join is on the ParentTable.ID and ChildTable.MainItemId
So I'm stuck on displaying the data is there a way of displaying the data like this through a sql query:
id, productname, itemnumber, amount, qty
id, mainitemid, productname, amount
id, mainitemid, productname, amount
id, mainitemid, productname, amount
id, mainitemid, productname, amount
id, mainitemid, productname, amount
id, productname2, itemnumber2, amount2, qty2
id, mainitemid2, productname2, amount2
id, mainitemid2, productname2, amount2
id, mainitemid2, productname2, amount2
id, mainitemid2, productname2, amount2
id, mainitemid2, productname2, amount2
id, productname3, itemnumber3, amount3, qty3
id, mainitemid3, productname3, amount3
id, mainitemid3, productname3, amount3
id, mainitemid3, productname3, amount3
id, mainitemid3, productname3, amount3
id, mainitemid3, productname3, amount3
Can you help me out on this? I hope this I've explained this properly. Let me know if you need more information.
Thanks!
James
May 4, 2006 at 4:48 pm
Use UNION of 2 queries with proper ORDER BY.
_____________
Code for TallyGenerator
May 4, 2006 at 9:08 pm
I'm not sure this will do the trick, because there are multiple products and then multiple parts to each product. These products all need to show with there parts under the associated product. Can you show me an example query?
Thanks!
James
May 5, 2006 at 3:26 am
This is about as close as you're going to get, but you're better off just doing a normal join and then doing the formatting in your front-end application.
If it appears I've misunderstood your requirement, please post some example data and the result you would want for that example data.
--data
declare @Parent Table (id int, productname varchar(10), itemnumber int, amount int, qty int)
insert @Parent
select 1, 'A', 1, 1, 1
union all select 2, 'B', 2, 2, 2
declare @child Table (id int, mainitemid int, productname varchar(10), amount int)
insert @child
select 3, 1, 'C', 3
union all select 4, 1, 'C', 4
union all select 5, 2, 'B', 5
--calculation
select id, null as childId, productname, itemnumber, amount, qty from @Parent
union all
select mainitemid, id, productname, null, amount, null from @child
order by id, childId
--suggestion
select * from @Parent p inner join @child c on p.id = c.mainitemid --use this style and format in the front-end
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply