Help with Lookup plzz - SQL Server Forums

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)

Help with Lookup plzz

Author

Topic

gems
Starting Member

19 Posts

Posted - 2007-03-21 : 09:32:45

Hi,

I am having real trouble with a lookup table. I have been asked to add a lookup table to the database so that users can use a field from that lookup table to run their reports. There are three fields that will be common in the new lookup table and the existing table. Below is some sample data.

Lookup table
Sector---type------ident---DC(new field)
EA11A----AA--------XYZ-----WW1
EA11A----AA--------ABC-----WW1
CE12B----AA--------CBAA----WW2
CE12B----BB--------DEF-----WW3

Existing table
Sector---type------ident
EA11A----AA--------XYZ
EA11A----AA--------XYZ
EA11A----AB--------XYZ
EA11B----AA--------ABC
CE12B----AA--------CBAA
CE12B----BB--------DEF
CE12B----BB--------DEF

All I want to do is that when a user enters a parameter for DC(new field) field it should pull up the right records from the existing table. I am having trouble doing that. The way I want to do the lookup is as follows –
If the user enter WW1 as a parameter, I should see

EA11A----AA--------XYZ
EA11A----AA--------XYZ
EA11A----AB--------XYZ
EA11B----AA--------ABC

I first need to do lookup by just Sector field. If that matches I need to pull up that record. If there is no matching Sector like ‘EA11B’ then I need to look for the other two fields together – ‘TYPE’ AND ‘IDENT’. ‘EA11B’ does not match in the lookup table but the other two fields do hence this record should be returned. I hope I am making sense. How should I do the join to get the right data?

Need help!!!

Thanks

-G

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 09:37:02

quote:
Originally posted by gems

Lookup table
Sector---type------ident---DC(new field)
EA11A----AA--------XYZ-----WW1
EA11A----AA--------ABC-----WW1
CE12B----AA--------CBAA----WW2
CE12B----BB--------DEF-----WW3

Yikes! This could be bad...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 09:40:19

[code]-- Prepare sample data
DECLARE @Lookup TABLE (Sector VARCHAR(5), Type VARCHAR(2), Ident VARCHAR(4), Dc VARCHAR(3))

INSERT @Lookup
SELECT 'EA11A', 'AA', 'XYZ', 'WW1' UNION ALL
SELECT 'EA11A', 'AA', 'ABC', 'WW1' UNION ALL
SELECT 'CE12B', 'AA', 'CBAA', 'WW2' UNION ALL
SELECT 'CE12B', 'BB', 'DEF', 'WW3'

DECLARE @Existing TABLE (Sector VARCHAR(5), Type VARCHAR(2), Ident VARCHAR(4))

INSERT @Existing
SELECT 'EA11A', 'AA', 'XYZ' UNION ALL
SELECT 'EA11A', 'AA', 'XYZ' UNION ALL
SELECT 'EA11A', 'AB', 'XYZ' UNION ALL
SELECT 'EA11B', 'AA', 'ABC' UNION ALL
SELECT 'CE12B', 'AA', 'CBAA' UNION ALL
SELECT 'CE12B', 'BB', 'DEF' UNION ALL
SELECT 'CE12B', 'BB', 'DEF'

-- Show the expected output, take 1
SELECT DISTINCT e.Sector,
e.Type,
e.Ident
FROM @Existing AS e
INNER JOIN @Lookup AS l ON l.Sector = e.Sector AND l.DC = 'WW1'

UNION ALL

SELECT DISTINCT e.Sector,
e.Type,
e.Ident
FROM @Existing AS e
INNER JOIN @Lookup AS l ON l.Type = e.Type AND l.Ident = e.Ident AND l.DC = 'WW1'

ORDER BY Sector,
Type,
Ident

-- Show the expected output, take 2
SELECT Sector,
Type,
Ident
FROM (
SELECT 0 AS Diff,
e.Sector,
e.Type,
e.Ident
FROM @Existing AS e
INNER JOIN @Lookup AS l ON l.Sector = e.Sector AND l.DC = 'WW1'

UNION

SELECT 1,
e.Sector,
e.Type,
e.Ident
FROM @Existing AS e
INNER JOIN @Lookup AS l ON l.Type = e.Type AND l.Ident = e.Ident AND l.DC = 'WW1'
) AS x
ORDER BY Sector,
Type,
Ident[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources