creatin' a view - 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

creatin' a view

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-03-26 : 16:04:48

Hi I have a table and I want to do a simple view.
it goes like this;
name city code
mike london T123
Sue Leeds
Fred London
Jim Luton T234
Dave Leeds T546
Bill Luton
Gary London
Julio Leeds
Gill Luton

you can see that only one representative from each city has the account code.
I would like to create my view so that eveyone from the same city has the code (see below)
name city code
mike london T123
Sue Leeds T546
Fred London T123
Jim Luton T234
Dave Leeds T546
Bill Luton T234
Gary London T123
Julio Leeds T456
Gill Luton T234

thanks for help in advance

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-26 : 16:18:52

Is this homework?


CREATE TABLE myTable99([name] varchar(50), city varchar(50), code varchar(50))
GO

INSERT INTO myTable99([name], city, code)
SELECT 'mike',  'london', 'T123' UNION ALL
SELECT 'Sue',   'Leeds',  null   UNION ALL 
SELECT 'Fred',  'London', null   UNION ALL
SELECT 'Jim',   'Luton',  'T234' UNION ALL
SELECT 'Dave',  'Leeds',  'T546' UNION ALL
SELECT 'Bill',  'Luton',  null   UNION ALL
SELECT 'Gary',  'London', null   UNION ALL
SELECT 'Julio', 'Leeds',  null   UNION ALL
SELECT 'Gill',  'Luton',  null
GO

   SELECT a.[name], a.city, b.code
     FROM myTable99 a 
LEFT JOIN (SELECT DISTINCT city,code 
	     FROM myTable99 
	    WHERE code IS NOT NULL) AS b
	ON a.city = b.city
GO

DROP TABLE myTable99
GO

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

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-03-26 : 16:49:00

whoa cowboy!!!
Not too fast, i'm not that bright!!!
it looks like you are selecting every row individually. I have about 56,000 rows in my table.
is there an easier way?
Much obliged!!!

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-26 : 16:55:03

Well, you still have to create the view on that, and all the rows won't be materialized until you select from it

CREATE VIEW myView AS...

And you still need a DISTINCT List of the codes...how many is that?

You could create a table of disctint codes, then place a trigger to fire and insert into that table when a new code is added, then just join to 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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-03-26 : 19:57:23

Sounds like a normalisation problem. Your city table does not have codes against it. Why not fix that?

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 09:33:53

quote:
Originally posted by LoztInSpace

Sounds like a normalisation problem. Your city table does not have codes against it. Why not fix that?

Well yes, if you take my 2nd suggestion about a 2nd table with a trigger, that would normalize it...but how does your data get in such a sate to begin with, AND how can you be sure the code is suppose to be the same for every city?

Seems pretty subjective...and as a matter of fact if you do


   SELECT city, code, COUNT(*)
     FROM myTable99
 GROUP BY city, code
   HAVING COUNT(*) > 1

I'll betcha you get some rows back

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 - 2007-03-27 : 09:50:27

Two other methods. It all depends on how many records there are to start with

SELECT		y.name,
		x.city,
		x.code
FROM		(
			SELECT		City,
					MAX(Code) AS Code
			FROM		MyTable99
			GROUP BY	City
		) AS x
INNER JOIN	MyTable99 AS y ON y.City = x.City

SELECT	t1.Name,
	t1.City,
	(SELECT MAX(t2.Code) FROM MyTable99 AS t2 WHERE t2.City = t1.City) AS Code
FROM	MyTable99 AS t1

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 09:53:09

Yes, but now you're being really subjective...how do you determine what data is more corrrect or has more value to it than the other?

Worse than that, what if it's the "wrong" value inadvertently?

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 - 2007-03-27 : 09:58:06

I just thought that a DISTINCT over City and Code could potentially produce dupe cities...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources