Towns & Spies << Cool SQL for Adults

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

Site Related Forums

The Yak Corral

Towns & Spies << Cool SQL for Adults

Author

Topic

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-19 : 16:31:25

create table towns (town varchar(8), country varchar(8))
create table spies (spy varchar(8), town varchar(8))
insert into towns
select 'Paris', 'France' union all
select 'Lion', 'France' union all
select 'Brest', 'France' union all
select 'NewYork', 'USA' union all
select 'Boston', 'USA' union all
select 'Seattle', 'USA' union all
select 'SanDiego', 'USA' union all
select 'Tokio', 'Japan' union all
select 'Kioto', 'Japan'
insert into spies
select 'Jay', 'Paris' union all
select 'Jay', 'Lion' union all
select 'Jay', 'Brest' union all
select 'Jay', 'Kioto' union all
select 'Brett', 'Boston' union all
select 'Brett', 'Kioto' union all
select 'Brett', 'Tokio' union all
select 'Brett', 'Paris'

Result should be (order does not matter):


spy      t/c  
-------- -------- 
Brett    Japan  << if a spy visited all towns of a country then "country"
Brett    Boston << otherwise - "town"
Brett    Paris
Jay      Kioto
Jay      France

Can here be a super brilliant solution or it is a tedious sucker?

Edit: I am not sure what is the "sucker".

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-01-19 : 19:58:57

well, it's not a super brilliant solution, but it does give the right answer...
select s.spy, t.town from spies s left join towns t on s.town = t.town
left join
(
select s.spy, t.country, count(*) as countof from spies s left join towns t on s.town = t.town
group by s.spy, t.country having count(*) > 1
) x
on s.spy = x.spy and t.country= x.country
where x.country is null

union

select distinct s.spy, t.country from spies s left join towns t on s.town = t.town
left join
(
select s.spy, t.country, count(*) as countof from spies s left join towns t on s.town = t.town
group by s.spy, t.country having count(*) = 1
) x
on s.spy = x.spy and t.country= x.country
where x.country is null
it's just your requirement restated.

You could of course simplify it by creating a view...or by doing it in a stored procedure and using table variables...but in case you want it as a "view"....

PS - robvolk can probably do it as a single line of code....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-19 : 22:11:29

Here's another way...

SELECT T.Town, T.Country, S.Spy
FROM Towns T
CROSS JOIN (SELECT DISTINCT Spy from Spies) S

-- ok, That's a rowset of every town and every spy (even if the spy was there or not)

-- It is easy to find the Spy names who have not visited every town in a country.

SELECT DISTINCT A.Country, A.Spy -- This will be a list of Countries where Spies have not visited all the towns.
FROM (
SELECT T.Town, T.Country, S.Spy
FROM Towns T
CROSS JOIN (SELECT DISTINCT Spy from Spies) S
) A
LEFT OUTER JOIN Spies S2 ON S2.Town = A.Town AND S2.Spy = A.Spy
WHERE S2.Spy IS NULL -- Any row with NULL indicates an incomplete COUNTRY

-- With this rowset, we have a control to generate the required result. We can either (1) UNION two separate selects, or (2) CASE to choose the result of Country or Town.


SELECT DISTINCT A.Spy, CASE WHEN C.Spy IS NOT NULL THEN B.Town ELSE B.Country END As [t/c]
FROM Spies A
INNER JOIN Towns B ON B.Town = A.Town
LEFT OUTER JOIN (
  SELECT DISTINCT A.Country, A.Spy -- This will be a list of Countries where Spies have not visited all the towns.
  FROM (
    SELECT T.Town, T.Country, S.Spy
    FROM Towns T
    CROSS JOIN (SELECT DISTINCT Spy from Spies) S 
  ) A
  LEFT OUTER JOIN Spies S2 ON S2.Town = A.Town AND S2.Spy = A.Spy
  WHERE S2.Spy IS NULL -- Any row with NULL indicates an incomplete COUNTRY
) C ON C.Country = B.Country AND C.Spy = A.Spy -- These columns are NOT NULL if the country is incomplete for this spy

Results:
Spy~t/c
Brett~Boston
Brett~Japan
Brett~Paris
Jay~France
Jay~Kioto

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-20 : 03:17:33

Funnily,
I chose the rrb's approach (w/o putting it on paper).
The Sam's looks "more set-based" one, so to speak.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-01-20 : 17:24:26

Why funnily?

No mine really is thoroughly set-based - it's just as you stated it, but restated in SQL. No cleverness. I try to leave being clever to other people - it gets me too confused....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-20 : 18:26:01

Rob's query is better - doesn't have a cross join like mine. Probably much faster for large rowcounts.

I wonder if anyone can improve on Rob's?

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-01-20 : 18:29:13

I suspect the optimiser will work it out - probably run the same either way....another reason why I don't bother trying to be clever...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-21 : 04:04:30

rrb,
the only thing that I found being funny was the same ways
of thinking, both your and mine, on the subject. Nothing more
than that. A propos. Do you mind my linguistic invalidness? Sic!
Plus, as extra bonus for smooth gliding along the f**king life (is
it life? Sure, Arnold Fribble's dogs conduct more.... ok, leave it),
a trace of

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-21 : 09:50:37

Hey .... I finally took a close look at rrb's query. It works for the sample data, but that's an accident.

The query assumes that if more than 1 city has been visited then the country is completed. Likewise, if only 1 city is completed, then the country is not completed. The sample data has no country with only 1 city listed, and no Spies who have visited more than 1 city but not all.

There's a way to correct rrb's query, and optimize the structure by constructing the "country complete" subquery only once for the entire recordset.

Sam

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-01-21 : 10:50:35

Well, I saw my name ( Cool I am a spy!! ) and had to give it a shot. Though I can't see a better way than Sams.

select 	distinct d.spy,
	case when towns_visited = towns_max then d.country else t.town end [t/c]
from 
(
	select d1.spy,d1.towns_visited,d2.towns_max,d1.country
	from
	(
		select 	spy,
			count(distinct s.town) towns_visited, 
			t.country
		from spies s
		join towns t on t.town = s.town
		group by s.spy, t.country
	) d1
	left join
	(
		select count(distinct town) towns_max, country
		from towns
		group by country
	) d2 on d2.country = d1.country
) d
join spies s on s.spy = d.spy 
join towns t on t.country = d.country and t.town = s.town

PS. I will work on that spooling...

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-21 : 13:57:33

Spy passions grow tenser.. Sharp eyesight you got, Sam Yakist.
============
Hey, ehorn! Your guess is correct - I meant namely you by that
"Jay the spy" from the spies table..............

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-01-21 : 17:20:39

Woops - I think I was having a bad day...<insert humorous comment about thinking that there were only two towns in the US>. when I went away and did it again, I came up with basically the same solution as ehorn.

select distinct s.spy, 
	case 
	when x.placesspied =  y.countoftowns then t.country
	else s.town 
	end
from
spies s inner join towns t
on s.town= t.town
left join
	(
	select s.spy, t.country, count(t.town) as placesspied
	from spies s inner join towns t
	on s.town = t.town
	group by s.spy, t.country
	) x
on s.spy = x.spy and x.country = t.country
inner join
	(
	select country, count(*) as countoftowns
	from towns
	group by country
	) y
on x.country = y.country

Apologies Stoad - I think I must have been in a rush!

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-21 : 17:58:48

Why did you move from a UNION to a CASE?

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-01-21 : 19:51:46

Why the change to a case? When I thought about it again, it just made sense, and I got a warm inner glow...

quote:
There's a way to correct rrb's query, and optimize the structure by constructing the "country complete" subquery only once for the entire recordset.

When are you going to reveal it?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-21 : 20:23:24

I am guessing that the UNION approach would be slower than the CASE approach.

Here's the UNION solution


SELECT X.Spy, X.Town
FROM (
	SELECT	DISTINCT Spy, S.Town, T.Country  -- Recordset of all Spies and all towns
		FROM	Spies S
		INNER JOIN Towns T ON T.Town = S.Town
	
	UNION ALL
	
	SELECT	DISTINCT Spy, T.Country As Town, Country -- Recordset of all Spies and all countries
		FROM	Spies S
		INNER	JOIN Towns T on T.Town = S.Town
		) X -- Recordset of all spies with all towns and all countries
INNER JOIN ( -- Recordset of Spy, TownsVisited and TotalTowns
	SELECT	Spy, C.Country, C.CountryTownCount, COUNT(*) As SpyTownCount
		FROM	Spies S
		INNER JOIN Towns T ON T.Town = S.Town
		INNER JOIN (
			SELECT	Country, COUNT(*) AS CountryTownCount
				FROM	Towns
				GROUP BY Country
			) C ON C.Country = T.Country
		GROUP BY Spy, C.Country, C.CountryTownCount
	) Y ON Y.Spy = X.Spy and Y.Country = X.Country

WHERE	(X.Town <> X.Country AND SpyTownCount <> CountryTownCount) -- Keep Town Rows with incompleted countries
OR	(X.Town =  X.Country AND SpyTownCount =  CountryTownCount) -- Keep Country rows with completed countries

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-01-21 : 20:35:13

Looks like it. I'm not really sure of the most valid way to compare execution plans, but running both queries together, the exe plan for the case is only 42%.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

groston
Yak Posting Veteran

61 Posts

Posted - 2004-02-13 : 14:53:56

I hope that some of your SQL gurus might still be watching this!

I am posting here because I think the answer will be similar ot the ones posted, but I can't quite wrap my head around it...

given:

tblResource:
columns: ResourceID, ResourceName, etc

tblSpecialResource:
columns: ResourceID, SpecialParms, etc

I would like to write a query that returns the following:
ResourceID, ResourceName, isSpecial

where isSpecial is a booleanthat is true if the resource appears in tblSpecialResource

Since I am strictly a novice at this, the obvious (but completely illegal) approach of retrieving data and setting a variable with the same select statement is that which occurred to me first.

Then, after finding this example, I did a select with a 'cross join' (my tables are quite small so performance is not an issue) and reasoned that I could extract the rows in which the ResourceID is the same, but this doesn't seem to do anything for me.

Would you please offer a suggestion.

Thanks

gerry@pairofdocs.net

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-13 : 18:43:09

select t.ResourceID, t.ResourceName,

case when tt.ResourceID is null then 0 else 1 end isSpecial

from tblResource t left join tblSpecialResource tt
on
t.ResourceID=tt.ResourceID

PS Maybe with distinct if any duplicates.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources