Need some recommendations

[excalibur78]

Here's what I need to do. Need to look in TableA and find out if a column is Yes or No. Then based on this look at TableB = Yes or TableC = No. Then in that table look at a date field. If it's null or not. Then go back to TableA and update fields to True or False bases on the results. I know I can do this in VB but I wasn't sure if I could use If lopp in a stored procedure and go that route. Any suggestions would be great.

 

[SQLSister]

Not sure exactly what you want but yes you can probably do it in a stored procedure and without a loop.
YOu never want to use loops if you can avoid them as they are extremely slow. YOU need to create set based statements.

Give us the structure of the tables and how they relate to each other and sample data nad the results you want and we can better help you.

 

[excalibur78]

Here's a the break down. All tables has common ID called "Store #". TableA has field "SCO". This field is either "Yes" or "No". Got to look at this and then goto TableB if it's "Yes" or TableC if it's "No". After we get this far we we need to evaluate if "Finished" is "True" or "False". Both TableB and TableC have this as well just 2 different sets of data with similar structures. If "Finished" = "True" then we need to set the field "Display" = "True" in our original TableA. If it is "False" we do nothing. Keep in mind "Store #" is the field they all have to tie to records together. If I could just see something similar and get an idea I'd be good but I can't visualize how to do it. Need more Pepsi I guess. LOL

Thanks

 

[SQLSister]

is the data in table ba nd c mutually exclusive or can a Store number appear in both tables?

 

[excalibur78]

It appears as the primary key on both tables. Same format and type.

 

[SQLSister]

BUt can the same store number be in both tables? Can store # 999 appear in both table b and table c?

 

[excalibur78]

Yes it can. More then likely it will.

 

[SQLSister]

OK lets try this. THis isn't tested.

UPDATE TableA
SET Display = "True'
WHERE TableA[Store #] IN
(SELECT TableA.[Store #] FROM TableA
JOIN
(SELECT [Store #], Finished,'b' AS TableID FROM TableB
UNION
SELECT [Store #], Finished, 'c' AS TableID FROM TableC)D
ON a.[Store #]= d[Store #]
WHERE (FINISHED = 'true' AND TableID = 'b' AND SCO = 'Yes')
OR (FINISHED = 'true' AND TableID = 'c' AND SCO = 'No'))

 

[excalibur78]

Thanks. Think I can make that work. Prolly won't be till tomorrow but I'll let you know how it goes.

 

[SQLSister]

Yeah it may not be exactly right, but it gives you a place to start from.

 

[excalibur78]

Yup helps a ton. Don't want you to write it for me just needed that idea. I can get it from here I think. Thanks again. I'll post the working code incase you need it someday or to help others. =)

 

[excalibur78]

I'm looking over the results but it looks to be good as follows:
UPDATE [Punchlist]
SET [Display] = 'True'
WHERE [Punchlist].[Store #] IN
(SELECT [Punchlist].[Store #] FROM [Punchlist]
JOIN
(SELECT [Store #], [Finished],'b' AS TableID FROM [POS_SCO Cutover2]
UNION
SELECT [Store #], [Finished], 'c' AS TableID FROM [POS Cutover])d
ON [Punchlist].[Store #]= d.[Store #]
WHERE ([Finished] = 'True' AND TableID = 'b' AND [SCO] = 'Yes')
OR ([Finished] = 'True' AND TableID = 'c' AND [SCO] = 'No'))