help with sql query a query

Question

hiya i have a massive job trying to sort thousands of records all is going okay, but i have a table of business and a table of users, now all the users need to have there business id applied. I have done this:

select b.id as businessid, u.id as userid
from business b, [trade_tools].[dbo].[user] u
where pcode = u.pword AND u.parent_id is null AND u.usertype_id = 5 AND u.display = 1 AND b.display = 1

this works great but i want to wrap this inside say:

update [trade_tools].[dbo].[user]
set parent_id = (select businessid FROM (select b.id as businessid, u.id as userid
from business b, [trade_tools].[dbo].[user] u
where pcode = u.pword AND u.parent_id is null AND u.usertype_id = 5 AND u.display = 1 AND b.display = 1) where userid =       currentIDWeoOnNow)

now i know this is possible i am just not working it the right way, could any SQL Pro's just lend a bit of a hand here so i can get this data to flow.

i did try running an update with an inner select however this did not work because the innrer select created more than 2 results when you are only allowed one, hence going down this route to try and get around that issue.

thanks p.s Server is MSSQL server and i am using SQL Server Management Studio to run my queries.

Could you give us the table definitions and say a word about usertype_id and display? I have a feeling that display at least is redundant. — Christian Severin
– Christian Severin, Commented Feb 16, 2011 at 10:54
usertype_id is business, admin, area manager etc in a seperate table, the display is to set the venue to active or not active neither of these effect what i am trying to do i am just limiting my results to specific users and businesses — davethecoder
– davethecoder, Commented Feb 16, 2011 at 11:02

Paolo Falabella · Accepted Answer · 2011-02-16 11:14:25Z

1

you have a one-to-many relationship from business to user (i.e. each business has many users but each user only belongs to one business)
you are using user.pword and business.pcode to join users to their business

In that case, doing simply this might work:

update [trade_tools].[dbo].[user]
set parent_id = businessid  
from business b, [trade_tools].[dbo].[user] u
where pcode = u.pword AND u.parent_id is null AND u.usertype_id = 5 AND u.display = 1 AND b.display = 1

answered Feb 16, 2011 at 11:14

Paolo Falabella

26k3 gold badges79 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

davethecoder Over a year ago

worked i think to an extent i still have 314 users that dont have a parent_id ( i.e Business ) but that could be a seperate issue and it has at least done a TON more, thankx for your help

Paolo Falabella Over a year ago

are these 314 results returned by your original SELECT? Otherwise try relaxing the constraints in the "where" clause of your select one at the time (i.e. try removing the b.display=1 and see if the select now returns your 314 users, if it doesn't try removing the u.display=1 and so on...)

MaryamAyd · Accepted Answer · 2011-02-16 11:23:55Z

1

I think this might work this time::

update [trade_tools].[dbo].[user] set parent_id = b.id

from business b, [trade_tools].[dbo].[user] u where pcode = u.pword AND u.parent_id is null AND u.usertype_id = 5 AND u.display = 1 AND b.display = 1 and u.userid =currentIDWeoOnNow

answered Feb 16, 2011 at 11:23

MaryamAyd

1511 silver badge9 bronze badges

1 Comment

MaryamAyd Over a year ago

How do you consider this condition in the right answer?'userid =currentIDWeoOnNow'

anothershrubery · Accepted Answer · 2011-02-16 11:35:58Z

0

Try this:

update [trade_tools].[dbo].[user] 
set parent_id = b.id

from 
business b, [trade_tools].[dbo].[user] u 
where pcode = u.pword 
AND u.parent_id is null 
AND u.usertype_id = 5 
AND u.display = 1 
AND b.display = 1 

where userid =currentIDWeoOnNow

edited Feb 16, 2011 at 11:35

anothershrubery

21.1k16 gold badges56 silver badges102 bronze badges

answered Feb 16, 2011 at 11:06

MaryamAyd

1511 silver badge9 bronze badges

1 Comment

anothershrubery Over a year ago

Also, correct syntax or not, try to use the code block for any code.

Collectives™ on Stack Overflow

help with sql query a query

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related