Going nuts over 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

SQL Server 2005 Forums

Transact-SQL (2005)

Going nuts over Query

Author

Topic

Sion01
Starting Member

6 Posts

Posted - 2010-05-03 : 16:56:42

Hi, im going nuts over this, ive tried everything and seems so simple to do..

I want to build a query that uses alternative to an inner join , because inner join dosent work after 'WHERE', wich i have to do.

I have something like this:

Players | Faults
----------------
IDPlayer| IDFault
Name | IDPlayer
| Gravity

I want to retrieve All players that have serious faults
(Gravity = 'S')

BUT build this "filter" AFTER a WHERE statemet, because i alredy have some oters 'Wheres'

Help? im going crazy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-03 : 17:01:06

Please show us a data example to make it more clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sion01
Starting Member

6 Posts

Posted - 2010-05-03 : 17:07:12

i have two tables

Players (IDPlayer,Name)
Faults (IDFault,IDPlayer,Gravity)

i need to add to an alredy existing query (SELECT * from player where name not like 'nameless' AND...

i need to put the rest of the query so that only it retreieves Players that have Faults = 'X'.

- I have tried inner join, but it dosent work because their dont work after 'WHERES'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-03 : 17:14:06

This?

select b.Name,a.IDFault,a.IDPlayer,a.Gravity
from Faults a
inner join Players b on a.IDPlayer = b.IDPlayer
where a.Gravity = 'X' and b.name not like 'nameless'

Sion01
Starting Member

6 Posts

Posted - 2010-05-03 : 17:41:51

quote:

Originally posted by vijayisonly

This?

select b.Name,a.IDFault,a.IDPlayer,a.Gravity
from Faults a
inner join Players b on a.IDPlayer = b.IDPlayer
where a.Gravity = 'X' and b.name not like 'nameless'

Thats the objective, but i need to have the inner join AFTER the 'WHERE', cause the query is alredy made until there, i need to complete it with that "filter", thats why i cant get it done :S

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-03 : 17:42:45

Please post a data example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-03 : 17:43:39

I have assumed

SELECT * from player where name not like 'nameless'

is your current WHERE clause? No? I have accounted for this in my code.

If this is not your whole query, please post your current query so that we can add this condition.

Sion01
Starting Member

6 Posts

Posted - 2010-05-03 : 18:16:13

quote:
Originally posted by vijayisonly

I have assumed
SELECT * from player where name not like 'nameless'
is your current WHERE clause? No? I have accounted for this in my code.

If this is not your whole query, please post your current query so that we can add this condition.

That query is the query i start with; now i have to add after the "AND" (continuing the query conditions) to only get Players with Faults = 'S', so in the ends its gonna be :

SELECT * from player where name not like 'nameless'

AND ETC.. (wich i cant figure out)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-03 : 18:35:47

So, you don't want to post sample data so what we can help you?

Maybe this link will help:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-04 : 09:51:49

quote:
Originally posted by Sion01

quote:
Originally posted by vijayisonly

I have assumed
SELECT * from player where name not like 'nameless'
is your current WHERE clause? No? I have accounted for this in my code.

If this is not your whole query, please post your current query so that we can add this condition.

That query is the query i start with; now i have to add after the "AND" (continuing the query conditions) to only get Players with Faults = 'S', so in the ends its gonna be :

SELECT * from player where name not like 'nameless'
AND ETC.. (wich i cant figure out)

If this is your existing query, it has already been accounted for.

select b.Name,a.IDFault,a.IDPlayer,a.Gravity
from Faults a
inner join Players b on a.IDPlayer = b.IDPlayer
where a.Gravity = 'X' and b.name not like 'nameless'

EDIT: If its not, then please provide sample data like everyone else has asked you...many times over.

mandm
Posting Yak Master

120 Posts

Posted - 2010-05-04 : 16:56:40

I think I know what the OP is saying. I've seen third party tools that will give you a portion of the query that you are not able to edit but you may customize what comes after it. In this case I think the following works

SELECT * from Players where name not like 'nameless' AND IDPlayer IN (SELECT IDPlayer FROM Faults WHERE Gravity = 'S')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources