two records in one to be displayed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

two records in one to be displayed

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-21 : 10:35:41

Hi guys
I wonder If I can have some quick fire help please?
I have a three table query suppliers and products with a intersection table called product suppliers.I wrotew the query below:

SELECT S.supplierNo, S.[Name], P.ProductCode, P.[Description], P.Storepack, p.[size], p.branAcostpr as [Jersey Cost],p.branBcostpr as [Guernsey Cost],'Duty' = case when ps.areacode = 002 then ps.duty as guernsey else ps.duty as jersey end,ps.freightcharge as Freight, p.retpr3 as [Checkers Jersey], p.retpr8 as [Checkers Guernsey]
FROM Supplier S inner join ProductSuppliers PS
on (S.supplierno = PS.suppliercode) inner join product p on (ps.productcode = p.productcode)
WHERE ps.MainSupplier = 'Y'
ORDER BY s.[Name]

I want to achieve one record for each product but the problem is in the productsuppliers tables there is two records for each product with a jersey duty and a guernsey duty so I am getting a duplicate product instead of a jerseyduty column and a guernseyduty column side by side for one product...

In red I tried to do a case statement which failed to work ..
would this be a possible soluton please ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-21 : 10:46:41

The red part should be

case when ps.areacode = 002 then ps.duty else ps.duty end as jersey ,

or

Duty=case when ps.areacode = 002 then ps.duty else ps.duty end,

Madhivanan

Failing to plan is Planning to fail

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-21 : 10:58:26

Cheers Madhi
But this is still giving me two records for each unique product because this products duty is stored in two records in the supplierproducts table.What I need is a guernsey duty and a jersey duty within the same record if you catch my drift.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-21 : 11:02:03

I am just guessing this is what you want.

SELECT	s.supplierNo, s.[Name], 
	p.ProductCode, p.[Description], p.Storepack, p.[size], 
	p.branAcostpr as [Jersey Cost], p.branBcostpr as [Guernsey Cost],
	ps.guernsey, ps.jersey,
	ps.freightcharge as Freight, p.retpr3 as [Checkers Jersey], p.retpr8 as [Checkers Guernsey]
FROM 	Supplier s inner join 
	(
		select	suppliercode, productcode, freightcharge,
			max(case when areacode = 002 then duty end) as guernsey,
			max(case when areacode <> 002 then duty end) as jersey,
		from	ProductSuppliers
		group by suppliercode, productcode, freightcharge
	) ps
	on	s.supplierno = ps.suppliercode
	inner join product p 
	on 	ps.productcode = p.productcode
WHERE 	ps.MainSupplier = 'Y'
ORDER BY s.[Name]

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-21 : 11:26:44

That is exactley what I needed thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources