Help with a SQL query... - 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

General SQL Server Forums

New to SQL Server Programming

Help with a SQL query...

Author

Topic

mozzer66
Starting Member

7 Posts

Posted - 2010-06-14 : 07:57:46

I'm not entirely a newbiw when it comes to SQL but I'm no expert. I'm quite a competent programmer and have come across a scenario for some code I'm trying to develop that I know I can do programatically but have a feeling I can do with SQL. I've trawled the books I've got but because I'm not sure what I'm looking for I don't know if I can't find it because it's not there or because I don't know what I'm looking for!

This is the problem - if someone can point me in the correct direction I'd be grateful.

I am trying to pull a list from a table that is a subset of the table. The selection I need is that I need all of the items based on one criteria (say country = "UK") but if one of the list items doesn't have a "UK" item then it needs a "DE" item, if the "DE" item doesn't exist then get the "CH" item, etc.

So if you picture a list of 10 things I need all of them if they exist from one country but if one or more of the items are missing for an item then there is a descending list of which substitute to get.

The challenge is the lists are long so I don't want to (unless of course I have to) do nested loops in ASP that pull each item one row at a time if there's a way of doing this elegantly with SQL.

The other problem is that for each country the subsitute list could vary. So the UK might have one, DE could have three and DK 5. If that makes sense.

Someone has suggested Transact-SQL but I wondered if it's possible with native SQL?

Thanks. Moz.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-14 : 08:03:07

It's not clear what you want. Please post a small sample set of your problem statement, like 10 or 15 records.

COALESCE(Table1.Col1, 'UK', 'DE', 'CH', 'SE') will return the first non-null item.

N 56°04'39.26"
E 12°55'05.63"

mozzer66
Starting Member

7 Posts

Posted - 2010-06-14 : 08:37:41

RowID / PositionID / Desc / Country
1 / 1 / Menu1 / UK
2 / 2 / Menu2 / UK
3 / 3 / Menu3 / UK
4 / 1 / Menu1 / DE
5 / 2 / Menu2 / DE
6 / 1 / Menu1 / CH

I think this is enough data to show what I'm looking for.

There is another table that describes the substitute list/order. In this scenario the UK's substitute list is just the UK. DE's is DE, then UK and CH's is DE and then UK.

The query is trying to pull back a list of each PositionID (1,2,3). For each position it wants the first substitute country and if it doesn't exist the next etc. etc.

If I could write my magical(!) query it would return the following RowID's.

UK: 1, 2, 3 ( because there is one of each for the UK )
DE: 4, 5, 3 ( because 1,2 exist for DE but not 3 so use the UK one)
CH: 6, 5, 3 ( because 1 exists for CH, 2 for DE and 3 for UK).

Thanks for your help,

Moz.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-14 : 11:11:09

Here is your first table. Please post your substitution table in same manner.

DECLARE	@Sample TABLE
	(
		RowID INT,
		PositionID INT,
		[Desc] VARCHAR(20),
		Country VARCHAR(20)
	)

INSERT	@Sample
	(
		RowID,
		PositionID,
		[Desc],
		Country
	)
SELECT	1, 1, 'Menu1', 'UK' UNION ALL
SELECT	2, 2, 'Menu2', 'UK' UNION ALL
SELECT	3, 3, 'Menu3', 'UK' UNION ALL
SELECT	4, 1, 'Menu1', 'DE' UNION ALL
SELECT	5, 2, 'Menu2', 'DE' UNION ALL
SELECT	6, 1, 'Menu1', 'CH'

N 56°04'39.26"
E 12°55'05.63"

mozzer66
Starting Member

7 Posts

Posted - 2010-06-14 : 11:55:30

Sorry I think I'm confusing you. The real table probably has 1000 rows in it. I need to query it sometimes leading from any of the 20 countries to find a list of 50 items where if it doesn't exist in one country I use the substitute list to find an alternative. Hence why a loop would easily achieve this, i.e., select if not there try the next alternative until one's found. But that seems ugly and intensive.

Does that maket more sense? I'm grateful you're trying to help. Cheers, Moz.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-14 : 13:39:01

Just a few records will do, as with the first sample set.
How do you else expect us to KNOW how your substitute order table is designed or even work?

N 56°04'39.26"
E 12°55'05.63"

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-14 : 14:05:54

quote:

I'm quite a competent programmer

I don't know anyone who is that would say that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mozzer66
Starting Member

7 Posts

Posted - 2010-06-14 : 16:18:02

Thanks for the persevering with me.

The other table looks like this

RowID / CountryID / LanguageID / Order
1 / UK / UK / 1
2 / DE / DE / 1
3 / DE / UK / 2
4 / CH / CH / 1
5 / CH / DE / 2
6 / CH / UK / 3

mozzer66
Starting Member

7 Posts

Posted - 2010-06-14 : 17:14:45

Now I've seen the second post I realise I'm doing this all wrong. Seems too complicated for someone with limited SQL skills. I thought this was the "new to SQL" section?

Oh well.

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-14 : 18:38:17

No Problem

NOW

Let's do THIS

Read my Hint Link in my SIG

Post what I ask there

You should hand an answer...in what Peso...2 minutes???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-15 : 03:38:21

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
RowID TINYINT NOT NULL,
PositionID TINYINT NOT NULL,
[Desc] CHAR(5) NOT NULL,
Country CHAR(2) NOT NULL
)

INSERT @Sample
(
RowID,
PositionID,
[Desc],
Country
)
SELECT 1, 1, 'Menu1', 'UK' UNION ALL
SELECT 2, 2, 'Menu2', 'UK' UNION ALL
SELECT 3, 3, 'Menu3', 'UK' UNION ALL
SELECT 4, 1, 'Menu1', 'DE' UNION ALL
SELECT 5, 2, 'Menu2', 'DE' UNION ALL
SELECT 6, 1, 'Menu1', 'CH'

-- Prepare substitute data
DECLARE @Substitute TABLE
(
RowID TINYINT NOT NULL,
CountryID CHAR(2) NOT NULL,
LanguageID CHAR(2) NOT NULL,
[Order] TINYINT NOT NULL
)

INSERT @Substitute
(
RowID,
CountryID,
LanguageID,
[Order]
)
SELECT 1, 'UK', 'UK', 1 UNION ALL
SELECT 2, 'DE', 'DE', 1 UNION ALL
SELECT 3, 'DE', 'UK', 2 UNION ALL
SELECT 4, 'CH', 'CH', 1 UNION ALL
SELECT 5, 'CH', 'DE', 2 UNION ALL
SELECT 6, 'CH', 'UK', 3

-- Display the wanted result
SELECT DISTINCT MAX(sa.RowID) OVER (PARTITION BY p.PositionID, sa.[Desc], c.Country) AS RowID,
p.PositionID,
sa.[Desc],
c.Country
FROM (
SELECT PositionID
FROM @Sample
GROUP BY PositionID
) AS p
CROSS JOIN (
SELECT Country
FROM @Sample
GROUP BY Country
) AS c
LEFT JOIN @Substitute AS su ON su.CountryID = c.Country
LEFT JOIN @Sample AS sa ON sa.PositionID = p.PositionID
AND sa.Country = su.LanguageID
WHERE sa.RowID IS NOT NULL
ORDER BY c.Country,
p.PositionID[/code]

N 56°04'39.26"
E 12°55'05.63"

mozzer66
Starting Member

7 Posts

Posted - 2010-06-15 : 05:01:53

Thanks for being patient with me. I've tried running this in MS SQL 2005 and "over" throws an error? I tried running all three together and one after another but although the first two worked the third always fails. Is this not supported by MS?

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'OVER'.
Msg 137, Level 15, State 2, Line 8
Must declare the variable '@Sample'.
Msg 137, Level 15, State 2, Line 13
Must declare the variable '@Sample'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-15 : 05:04:34

If you are using SQL Server 2005 or later (which I assume), you have to also set the current compatibility level to 90 or higher for your database.

N 56°04'39.26"
E 12°55'05.63"

mozzer66
Starting Member

7 Posts

Posted - 2010-06-15 : 08:16:40

I got it to work - brilliant thank you.

Am I correct in thinking that the SQL assumes there is not ever more than 3 substitute languages? Is it possible to construct it so that it works for "N"? Again in programming terms I could do a loop (while/for/etc.).

As you've probably gathered this is for language switching the content on a home made CMS. We're trying to allow the languages that already exist to be switched between sites where there is a pre-defined list of alternative languages. It's likely it will only ever be 2 but it's possible that someone might want to switch between 5. I'd rather not limit the number of languages progratically if it can be data driven.

Cheers again - much appreciate. Moz.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-15 : 09:30:09

It will automatically adjust itself for more languages.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources