Create 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

Create View

Author

Topic

irvinmetcalf
Starting Member

4 Posts

Posted - 2014-10-06 : 19:25:13

Hi,
I want to create a view from table j25_teamlist2014 with the 7 columns shown below. But I only want the rows which don't have a Status of 'Not Available'.

This sql works fine apart from the Team Name and Team Captain columns display the team which is an interger
ie Row 1 shows 1 in the Team Name and Team Captain columns instead of 'My Team' and 'Jo Bloggs'
Also when viewing the original table and the new view the structures are slightly different.

Any help would be very much appreciated.
BR Irvin

CREATE VIEW j25_availableteamlist2014 AS
SELECT Team, Day, Time, Lane, Team Name, Team Captain, Status
FROM j25_teamlist2014
WHERE Status NOT LIKE 'N%'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-06 : 19:29:53

You'll need to show us sample data as I can't understand your second paragraph.

CREATE VIEW j25_availableteamlist2014
AS
SELECT Team, Day, Time, Lane, Team Name, Team Captain, Status
FROM j25_teamlist2014
WHERE Status <> 'Not Available'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

irvinmetcalf
Starting Member

4 Posts

Posted - 2014-10-07 : 05:09:49

Thanks for your interest. Tara. I hope this below explains my problem in a bit more detail.

SQL Query used to CREATE VIEW:
CREATE VIEW j25_availableteamlist2014 AS
SELECT Team, Day, Time, Lane, Team Name, Team Captain, Status
FROM j25_teamlist2014
WHERE Status NOT LIKE 'N%'

Table j25_teamlist2014
Column Names: Team, Day,Time,Team Name, Team Captain, Status
"001","Fri 06Feb15","13:30","1","GANF - Ambergate","Anita Asken","Confirmed"
"002","Fri 06Feb15","13:30","2","GANF - Ambergate","Anita Asken","Provisional"
"003","Fri 06Feb15","13:30","3","Ange's Crew","Angela Taylor","Not Available"
"004","Fri 06Feb15","13:30","4","The Aquatic Baptists","David Holmes","Free"
"005","Fri 06Feb15","13:30","5","The Phoenix School ","Jackie McLean","Provisional"
etc.

So the sql above leaves out the rows with a status of 'Not Avalaible' but somehow replaces the Team Name and Team Captain details with a number rather than the VARCHAR string.

View Table:
Column Names: Team, Day,Time,Team Name, Team Captain, Status
"001","Fri 06Feb15","13:30","1","001","001","Confirmed"
"002","Fri 06Feb15","13:30","2","002","002","Provisional"
"004","Fri 06Feb15","13:30","4","004","004","Free"
"005","Fri 06Feb15","13:30","5","005","005","Provisional"
etc.
Thanks for your time
BR Irvin Metcalf

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-07 : 06:53:58

[code]SELECT Team, Day, Time, Lane, [Team Name], [Team Captain], Status[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

irvinmetcalf
Starting Member

4 Posts

Posted - 2014-10-07 : 07:58:33

Hi SwePeso, Thanks for your interest.

Your possible solution:- SELECT Team, Day, Time, Lane, [Team Name], [Team Captain], Status with the square brackets
Gives the following! :(

"001","Fri 06Feb15","13:30","1","Team Name","Team Captain","Confirmed"
"002","Fri 06Feb15","13:30","2","Team Name","Team Captain","Provisional"
"003","Fri 06Feb15","13:30","3","Team Name","Team Captain","Free"

irvinmetcalf
Starting Member

4 Posts

Posted - 2014-10-07 : 08:20:23

Hi Tara and SwePeso.

OK I've cracked it.
CREATE VIEW j25_availableteamlist2014 AS
SELECT *
FROM j25_teamlist2014
WHERE Status <> 'Not Available'

Thanks for all your help in pointing me in the right direction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources