T-SQL script not quite what I want

Question

T-SQL script not quite what I want

Viewing 7 posts - 16 through 21 (of 21 total)

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

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 1

Here is the sample data. Neither one of these clients bought bicycles. I have added the customer part to the query as well and still get the same results.

Name Client_partnerPartner Description Date OpenedDate Closed

GLOBAL PTY LTDRolf LarsenRolf Larsen Helmut 01/02/2009NULL

GLOBAL PTY LTDRolf LarsenRolf Larsen Gloves 01/02/2009NULL

GLOBAL PTY LTDRolf LarsenRolf Larsen ZZZ_Pegs 22/09/200801/02/2009

LIMITED Rolf LarsenRolf Larsen ZZZ_Pegs 26/08/200801/02/2009

LIMITED Rolf LarsenDavid Wilson Helmut 01/02/2009NULL

Sorry if I'm being a bit ditsy, but I feel like I've been staring at this forever. It's starting to get blurry

Thanks,

Kris

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 2

Thanks Jamie. That's seems to have done the trick. Now I just need to get my where clause to exclude those descriptions with zzz and yyy at the beginning of them.

Thanks again for your help

Thanks,

Kris

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 3

Thanks everyone for your help. It's much appreciated. I have my final where clause all sorted now.

Thanks again.

Thanks,

Kris

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Kris,

Glad you got you query problem solved. Now, I'd like you to carefully read the first article I have referenced in my signature block below. When you have a query problem, please follow the guidelines in that article when posting for help.

For example, the sample data you finally posted for us here? Unusable without us editting it. You need to provide the data in a readily consumable format so that all we have to do is cut, paste, and execute to load into the table(s) that you also provided the DDL (CREATE TABLE statements) for us to create the tables.

In addition, there have been known instances of people going through the process to provide us with everything to help them, and they solved their own problem, so it benefits you as well.

Last thing, please post your final code. It is proper etiquette, and it may help others with a simalar issue as you had.

dhulme Mr or Mrs. 500 Points: 570 More actions · Answer 5

Kris (5/24/2009)
That's awesome. Thank you so much. I don't want to push the friendship but they have just now asked me if I can exlude anything that starts with ZZZ and ZZY in the Description.

Interesting requirement. Here's a suggestion:

alter table dbo.TheLookup

add flagExclude bit null

go

update dbo.TheLookup

set flagExclude = 1

Where left(description,3) in ('zzz','zzy')

go

Then change the selection of descriptions to exclude any where flagExclude = 1

dhulme Mr or Mrs. 500 Points: 570 More actions · Answer 6

Kris (5/26/2009)
Sorry to be a pain, but before I've even add the extra where clauses it doesn't appear to be giving me the correct results. I get 90000 records back when I should get back about 500. I checked the first record and there is no bicycle under that customer so I'm not sure when it's getting. Can you please help me out.

All those inner joins might be creating a cartesian result.

To debug, you should select the smallest possible dataset to identify the customers you are interested in, so:

select count(1)

from customer c (nolock)

where exists (Select top 1 1 from Orders o (nolock) where Product = 'Bicycle' and o.CustomerID = c.CustomerID)

Progressively add the other tables until you find the numbers go nuts. Then you've found the one-to-many relationship to resolve.

You might want to subquery the offending join to stop Captain Cartesian coming to your party:

...inner join (select top 1 id, description from lookup where condition) as lookup

on source.id = lookup.id

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 7

jamie (5/27/2009)
You might consider trying (based on the last query posted) to remove that exists piece to stop your query from running many sub-queries against your database (it will run a query for every customer in your top query). Moving it to the inner join piece will cause it to only run once bringing back only customers that have bicycle .. This might not work well depending on your indexes.
SELECT Customer.Name AS SortName,
vcltAttrib4_364.ainTVal AS Client_Relationship_Partner,
vFEAttrib4.attInstName AS Partner,
Matter.mtrDesc AS Description,
Matter.mtrDateOpened AS Date_Opened,
Matter.mtrDateClosed AS Date_Closed
FROM Customer
INNER JOIN Matter ON Customer.objInstID=Matter.mtrClientID
INNER JOIN vcltAttrib4 AS vcltAttrib4_364 ON Customer.objInstID=vcltAttrib4_364.ObjectInstID
INNER JOIN vFEAttrib4 ON Matter.mtrPartnerID=vFEAttrib4.attInstID
INNER JOIN cdbEntity ON Matter.mtrEntityID=cdbEntity.entID
INNER JOIN (SELECT DISTINCT c.Name
FROM Customer c
INNER JOIN Matter m ON m.mtrClientID = c.objinstID
INNER JOIN cdbEntity e ON e.entID = m.mtrEntityID
WHERE e.SortName = 'Bicycle') filter on filter.Name=Customer.Name
ORDER BY Customer.Name

Hi Jamie,

I just wanted to point out that because the EXISTS is correlated it does not necessarily run for every row in the outer set.

In fact a lot of the time you will find that using an exists like this cause the optimizer will actually do a semi-join which more often that is faster than a normal join.

remember to always test the different solutions for performance before deciding which is best for you situation:-)

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life