Problem creating 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

Problem creating view

Author

Topic

Idunnit1
Starting Member

8 Posts

Posted - 2010-02-21 : 14:38:11

Hi I wonder if there is someone out there that could help,

I have 3 tables with the following

qatar_total_points
user....qatar_points
63..........2
64..........1
71..........1
72..........1

motegi_total_points
user....motegi_points
63..........1
64..........2

jerez_total_points
user....jerez_points
63.........3
64.........3

What I am trying to do but failing is to create a view like so:

Player_total_points
user....qatar_points....motegi_points...jerez_points
63...........2...............1..............3
64...........1...............2..............3
71...........1...............0..............0
72...........1...............0..............0

Can anyone help me out there

Cheers
Idunnit1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-21 : 14:46:08

[code]select a.user
,a.qatar_points as [qatar_points ]
,coalesce(b.motegi_points,0) as [motegi_points]
,coalesce(c.jerez_points,0) as [jerez_points]
from qatar_total_points a
left join motegi_total_points b on a.user = b.user
left join jerez_total_points c on a.user = c.user
order by a.user[/code]

Idunnit1
Starting Member

8 Posts

Posted - 2010-02-21 : 15:25:15

Thanks for the quick reply,

I have tried your code but a syntex error apears.

I have tried the code without the first part like so:

select a.`user`
, `qatar_points`, `motegi_points`, `jerez_points`
from qatar_total_points AS a
left join `motegi_total_points` b on a.`user` = b.`user`
left join `jerez_total_points` c on a.`user` = c.`user`
order by a.user

This gives me the correct view but with the null instead of 0

I think it has something todo with the use of [] but not sure....cant get it to work

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-21 : 15:27:12

The COALESCE function was added to replace the NULL value with '0'.

Whats the error you received? I hope you're using SQL Server.

Idunnit1
Starting Member

8 Posts

Posted - 2010-02-21 : 15:29:17

Got it

I replaced the [ with `

superb

thank you very much

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-21 : 15:33:48

using '[' shouldn't cause you any errors?..Are you sure thats the only changed you made?

Idunnit1
Starting Member

8 Posts

Posted - 2010-02-21 : 16:48:06

Yes I only changed the [ for ` on all 6 of them.....after that it worked fine.

I am using PhpMyAdmin........I know lots of people has said its rubbish but I have yet to get any other one to work.

Thanks again

Kristen
Test

22859 Posts

Posted - 2010-02-22 : 00:54:36

The ` characters is what MySQL uses to delimit column names. Thus this is MySQL rather than MS SQL

@Idunnit1: This is a Microsoft SQL Server forum, thus there may not be people here who are able to help you with MySQL

I think www.MySQL.com has a forum, which may get you better answers.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-22 : 03:37:42

quote:
Originally posted by vijayisonly

using '[' shouldn't cause you any errors?..Are you sure thats the only changed you made?

It wont work in MySQL

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-22 : 03:38:49

quote:
Originally posted by Idunnit1

Yes I only changed the [ for ` on all 6 of them.....after that it worked fine.

I am using PhpMyAdmin........I know lots of people has said its rubbish but I have yet to get any other one to work.

Thanks again

You dont need to use as the column names dont have special characters like space,etc

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 09:19:36

quote:
Originally posted by madhivanan

quote:
Originally posted by vijayisonly

using '[' shouldn't cause you any errors?..Are you sure thats the only changed you made?

It wont work in MySQL

Madhivanan

Failing to plan is Planning to fail

Well..in my earlier reply...I had aksed the OP if he is using SQL server...he did not respond to that..so I kinda assumed he was using it.

Idunnit1
Starting Member

8 Posts

Posted - 2010-02-22 : 16:16:42

Hi all,

Why do I get the feeling I'm not welcome. I am new to all this and I thought sql was well sql. I didnt relize there is such varieties of it. I didnt intend to get people upset and anoyed at my post. At the end of the day you guys helped me alot. I have fixed the problem because of your help. I shall take on board what you have said.

Cheers
Idunnit1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 16:24:18

Idunnit...Nobody is upset with you....In fact, I dont see anything in this thread that sounds offending to anyone...

You are most welcome here...Please do continue to post your questions.
The main reason the forum is available is to help people with their SQL.

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 02:29:07

Indeed, I'm certainly not upset, I'm just doubtful that we will be much help as there are few people here (that I know of) who know anything much about MySQL.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 03:40:57

<<
Why do I get the feeling I'm not welcome.
>>

I indirectly welcomed you by answering your question. See my second reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources