Complicated join...

Question

Complicated join...

Viewing 15 posts - 31 through 45 (of 58 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323755 More actions · Answer 1

mikeincape (5/9/2011)
Yes... This gives me the dataset I need. Thanks!... Still getting that weird error though... Any Ideas?

Ninja's is going to kick me for repeating what he already proved wrong...but i still say it's compatibility;

can you double check it?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

alan.williams (5/9/2011)
Why not just do a Union of the two queries

Delete that line

WHERE RW = 1

mikeincape Old Hand Points: 367 More actions · Answer 3

Are you using any 3rd party application that refactors sql?

Using SQL management Studio to execute the queries you are sening me... No 3rd party apps anywhere.

Also, we've done it by date but you're doing it by id in the original code. My gut tells me that the date is actually the correct sorting, then maybe flagid as tie breaker.

FlagID or Date DESC... Both would produce the same result... The last flag to match that code.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Lowell (5/9/2011)
mikeincape (5/9/2011)
Yes... This gives me the dataset I need. Thanks!... Still getting that weird error though... Any Ideas?
Ninja's is going to kick me for repeating what he already proved wrong...but i still say it's compatibility;
can you double check it?

I'd never do that. Ok maybe but still, that's uncalled for :hehe:.

It's definitely possible, but if he could run my create db code, then I don't see why it would fail there (never seen that before).

The only thing I can think off is to put model at level 80 and try my code again. Maybe there's a weird difference in sys tables that makes this fail.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

mikeincape (5/9/2011)
Are you using any 3rd party application that refactors sql?
Using SQL management Studio to execute the queries you are sening me... No 3rd party apps anywhere.
Also, we've done it by date but you're doing it by id in the original code. My gut tells me that the date is actually the correct sorting, then maybe flagid as tie breaker.
FlagID or Date DESC... Both would produce the same result... The last flag to match that code.

I'm just saying, I'v seen tones of apps that allow to work in a different workday. So on the 1st of the month you can still enter documents for the 31st of the last month. In that case sorting by date, then flag id is the correct way to do it. Not just flagid.

Lowell SSC Guru Points: 323755 More actions · Answer 6

Ninja's_RGR'us (5/9/2011)
It's definitely possible, but if he could run my create db code, then I don't see why it would fail there (never seen that before).
The only thing I can think off is to put model at level 80 and try my code again. Maybe there's a weird difference in sys tables that makes this fail.

could it be his 2005 client tools are returning an error but still executing the code, where our 2008 tools either never raise the error or suppress it?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Lowell (5/9/2011)
Ninja's_RGR'us (5/9/2011)
It's definitely possible, but if he could run my create db code, then I don't see why it would fail there (never seen that before).
The only thing I can think off is to put model at level 80 and try my code again. Maybe there's a weird difference in sys tables that makes this fail.
could it be his 2005 client tools are returning an error but still executing the code, where our 2008 tools either never raise the error or suppress it?

I'm on 2005 both for tools and engine.

What bugs me is that the code gets refactored. SSMS will NOT do that on it's own. Something must be installed to do that.

mikeincape Old Hand Points: 367 More actions · Answer 8

Lowell (5/9/2011)
mikeincape (5/9/2011)
Yes... This gives me the dataset I need. Thanks!... Still getting that weird error though... Any Ideas?
Ninja's is going to kick me for repeating what he already proved wrong...but i still say it's compatibility;
can you double check it?

I am definitely in 90 mode...

mikeincape Old Hand Points: 367 More actions · Answer 9

Ninja's_RGR'us (5/9/2011)
Lowell (5/9/2011)
Ninja's_RGR'us (5/9/2011)
It's definitely possible, but if he could run my create db code, then I don't see why it would fail there (never seen that before).
The only thing I can think off is to put model at level 80 and try my code again. Maybe there's a weird difference in sys tables that makes this fail.
could it be his 2005 client tools are returning an error but still executing the code, where our 2008 tools either never raise the error or suppress it?
I'm on 2005 both for tools and engine.
What bugs me is that the code gets refactored. SSMS will NOT do that on it's own. Something must be installed to do that.

It's always bothered me that it does that... But its done it on all 3 of the servers I have installed it on, and my copy of management express I am using on my local PC here. What I have been doing today is from management express on my local pc. Think I should login to my server and use the full version of sql management studio?

Local software:

Microsoft SQL Server Management Studio Express9.00.2047.00

Microsoft Data Access Components (MDAC)6.1.7601.17514 (win7sp1_rtm.101119-1850)

Microsoft MSXML3.0 4.0 5.0 6.0

Microsoft Internet Explorer8.0.7601.17514

Microsoft .NET Framework2.0.50727.5444

Operating System6.1.7601

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

We're missing something really simple. Like running on wrong server, wrong DB, stupid syntaxe issue. Maybe a trigger somewhere?!?!?

Are you running something else in that batch?

I'm going to say it again. The code won't magically reformat itself. You have a 3rd party application doing that for you.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

mikeincape (5/9/2011)
Ninja's_RGR'us (5/9/2011)
Lowell (5/9/2011)
Ninja's_RGR'us (5/9/2011)
It's definitely possible, but if he could run my create db code, then I don't see why it would fail there (never seen that before).
The only thing I can think off is to put model at level 80 and try my code again. Maybe there's a weird difference in sys tables that makes this fail.
could it be his 2005 client tools are returning an error but still executing the code, where our 2008 tools either never raise the error or suppress it?
I'm on 2005 both for tools and engine.
What bugs me is that the code gets refactored. SSMS will NOT do that on it's own. Something must be installed to do that.
It's always bothered me that it does that... But its done it on all 3 of the servers I have installed it on, and my copy of management express I am using on my local PC here. What I have been doing today is from management express on my local pc. Think I should login to my server and use the full version of sql management studio?
Local software:
Microsoft SQL Server Management Studio Express9.00.2047.00
Microsoft Data Access Components (MDAC)6.1.7601.17514 (win7sp1_rtm.101119-1850)
Microsoft MSXML3.0 4.0 5.0 6.0
Microsoft Internet Explorer8.0.7601.17514
Microsoft .NET Framework2.0.50727.5444
Operating System6.1.7601

Yes please.

Also what does that return?

SELECT @@version

mikeincape Old Hand Points: 367 More actions · Answer 12

Ahhh Ha!

Ok.... Runs fine on my server. Must be from using the express version on my local pc...

Microsoft SQL Server Management Studio9.00.4035.00

Microsoft Analysis Services Client Tools2005.090.4035.00

Microsoft Data Access Components (MDAC)2000.086.3959.00 (srv03_sp2_rtm.070216-1710)

Microsoft MSXML2.6 3.0 4.0 6.0

Microsoft Internet Explorer8.0.6001.18702

Microsoft .NET Framework2.0.50727.3620

Operating System5.2.3790

Thanks for all the help!

Now if we want to get real complicated I have one more query that needs to happen in the loop... Think we can pile more onto this query? I will post what I need in a reply.

BTW: The query I am using that seems to work best is:

SELECT

cl.RecordStatus,

cl.ActiveClient,

cl.SystemEffective,

cl.Firstname,

cl.Lastname,

cl.MiddleI,

cl.SSN,

cl.ClientID,

cl.City,

cl.State,

cl.Zip,

cl.HomePhone,

cl.Address1,

bg.FlagDate as EnteredDate,

fl.FlagDate as MoneyReceived

FROM Clients cl

LEFT OUTER JOIN

(SELECT

ClientID,

FlagDate

FROM Flags

WHERE (FlagCode=1001)

) bg

ON cl.ClientID = bg.ClientID

LEFT OUTER JOIN

(SELECT

ClientID,

FlagDate

FROM (SELECT

ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY ClientID,FlagCode desc,FlagDate DESC) AS RW,

ClientID,

FlagDate

FROM Flags

WHERE (FlagCode=3001 OR FlagCode=3002)

)myAlias

WHERE RW = 1

) fl

ON cl.ClientID = fl.ClientID

WHERE cl.AgentID=20

ORDER BY

cl.Lastname,

cl.Firstname,

cl.MiddleI

Lowell SSC Guru Points: 323755 More actions · Answer 13

mikeincape (5/9/2011)
Ahhh Ha!
Ok.... Runs fine on my server. Must be from using the express version on my local pc...

[fist pump] YES!!!!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Lowell (5/9/2011)
mikeincape (5/9/2011)
Ahhh Ha!
Ok.... Runs fine on my server. Must be from using the express version on my local pc...
[fist pump] YES!!!!

BOTH hands on the keyboard plz! :smooooth:

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

mikeincape (5/9/2011)
Ahhh Ha!
Ok.... Runs fine on my server. Must be from using the express version on my local pc...
Microsoft SQL Server Management Studio9.00.4035.00
Microsoft Analysis Services Client Tools2005.090.4035.00
Microsoft Data Access Components (MDAC)2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML2.6 3.0 4.0 6.0
Microsoft Internet Explorer8.0.6001.18702
Microsoft .NET Framework2.0.50727.3620
Operating System5.2.3790
Thanks for all the help!
Now if we want to get real complicated I have one more query that needs to happen in the loop... Think we can pile more onto this query? I will post what I need in a reply.
BTW: The query I am using that seems to work best is:
SELECT
cl.RecordStatus,
cl.ActiveClient,
cl.SystemEffective,
cl.Firstname,
cl.Lastname,
cl.MiddleI,
cl.SSN,
cl.ClientID,
cl.City,
cl.State,
cl.Zip,
cl.HomePhone,
cl.Address1,
bg.FlagDate as EnteredDate,
fl.FlagDate as MoneyReceived
FROM Clients cl
LEFT OUTER JOIN
(SELECT
ClientID,
FlagDate
FROM Flags
WHERE (FlagCode=1001)
) bg
ON cl.ClientID = bg.ClientID
LEFT OUTER JOIN
(SELECT
ClientID,
FlagDate
FROM (SELECT
ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY ClientID,FlagCode desc,FlagDate DESC) AS RW,
ClientID,
FlagDate
FROM Flags
WHERE (FlagCode=3001 OR FlagCode=3002)
)myAlias
WHERE RW = 1
) fl
ON cl.ClientID = fl.ClientID
WHERE cl.AgentID=20
ORDER BY
cl.Lastname,
cl.Firstname,
cl.MiddleI

Sure, what else do you need?