May 24, 2009 at 7:43 pm
I’m trying to write a script that filters out clients that have a certain transaction type however if they have that transaction type I don’t want it to just show that transaction but all of them regardless of the transaction type.
For example show me every customer that bought a bike and show everything else that customer also bought.
Hope that makes sense.
Kris
May 24, 2009 at 7:48 pm
Makes perfect sense - now, can you show what you have tried so far? Please read the article I link to in my signature about how to get better answers and post some create statements, sample data and expected results and somebody here will surely help you out.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 24, 2009 at 8:35 pm
This is what I have so far.
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 LEFT OUTER JOIN Matter ON Customer.objInstID=Matter.mtrClientID)
LEFT OUTER JOIN vcltAttrib4 AS vcltAttrib4_364 ON Customer.objInstID=vcltAttrib4_364.ObjectInstID)
LEFT OUTER JOIN vFEAttrib4 ON Matter.mtrPartnerID=vFEAttrib4.attInstID)
LEFT OUTER JOIN cdbEntity ON Matter.mtrEntityID=cdbEntity.entID)
WHERE ((cdbEntity.SortName ='bicycle'))
ORDER BY Customer.Name
Kris
May 24, 2009 at 8:49 pm
Kris (5/24/2009)
This is what I have so far.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 LEFT OUTER JOIN Matter ON Customer.objInstID=Matter.mtrClientID)
LEFT OUTER JOIN vcltAttrib4 AS vcltAttrib4_364 ON Customer.objInstID=vcltAttrib4_364.ObjectInstID)
LEFT OUTER JOIN vFEAttrib4 ON Matter.mtrPartnerID=vFEAttrib4.attInstID)
LEFT OUTER JOIN cdbEntity ON Matter.mtrEntityID=cdbEntity.entID)
WHERE ((cdbEntity.SortName ='bicycle'))
ORDER BY Customer.Name
Okay, first we can get rid of all of the outer joins. By adding the criteria on the last table in the outer join, you are effectively turning them all into inner joins.
One way to get the results you are looking for is to use EXISTS - as in:
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
WHERE EXISTS (SELECT *
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')
ORDER BY Customer.Name
Other methods include using a common table expression (cte) or derived table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 24, 2009 at 9:40 pm
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.
Thanks again for your quick and very helpful response
Kris
May 24, 2009 at 11:27 pm
Depends on where the Description field is - and whether or not it is related to the SortName. If not related, just add an AND to the where clause before the exists:
WHERE Description NOT LIKE 'ZZZ%'
AND EXIST (...)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 26, 2009 at 9:39 pm
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.
Kris
May 26, 2009 at 9:48 pm
It would help if you would provide the DDL for the tables and views (including base tables for the views) involved in the query. Some sample data for the tables in a readily consummable format, and the expected results based on the sample data.
If you read the first article (same one you were pointed to above actually) referenced in my signature block, you will see how best to post information for questions like this that get better answers faster.
May 27, 2009 at 2:54 am
Jeffrey Williams (5/24/2009)
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
WHERE EXISTS (SELECT *
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')
ORDER BY Customer.Name
I think you forgot to add "and Customer.Name = c.Name" (or customerid if it exists) in the exists where clause.
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
WHERE EXISTS (SELECT *
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' and Customer.Name = c.Name)
ORDER BY Customer.Name
May 27, 2009 at 8:26 am
Kupy, yes that would probably be a good idea to correlate the exists query back to the customer. It definitely would have been seen with sample data 😉
Edit: Sorry Kupy, for some reason thought Lynn made the comment.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2009 at 8:32 am
Sorry guys, but I don't know what sample data would do when it's wrong.....I want every customer that has bought a bicycle and every other purchase too.....if they haven't bought a bike then I don't need to see it.....I also want to see everything doesn't include zzz
Kris
May 27, 2009 at 8:34 am
Kris (5/27/2009)
Sorry guys, but I don't know what sample data would do when it's wrong.....I want every customer that has bought a bicycle and every other purchase too.....if they haven't bought a bike then I don't need to see it.....I also want to see everything doesn't include zzz
Sample data would give us something to test our code against. You really have a choice, you can have code that doesn't meet your requirements or you can have code that does. Which would you prefer to have?
May 27, 2009 at 8:55 am
Sample data of what is in the tables, and what the result should be. Not what you are getting with the query that doesn't work.
May 27, 2009 at 9:06 am
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
May 27, 2009 at 9:22 am
Two-step it:
1. Write query to pull every customer that bought a bike, do it as a cte or store results in a temp table.
2. Join the cte or the temp table to your items ordered table on what is presumably a customer number.
Want code? Post some sample data.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply