November 30, 2010 at 12:07 pm
I have a view that currently has 82 rows in it. I need to join them to another table to see if any of them are members of BBB (noted by Sic_Code = 'MTA5'). In the table, there are multiple rows for each member (BACKGROUND, some members are BBB, some 'Like Golf', or 'Has Kids' I know it may not be the best means of storing, but it is what it is). I ultimately need two queries, one for BBB members and one for non-BBB members. I am not the best with JOINS and will admit Query Builders, as nice as they are, in the end create poor query writers. As we use them instead of thinking. A little more background. I am writing a process that gathers information to create their member cards. The BBB members are printed/programmed on a pre-printed card with the BBB logo on it.
Here is my query for BBB members:
SELECT vw.Trade_ID, vw.Account, vw.Business_Name, vw.cardfirst, vw.New_Card, vw.Date_Issued, vw.Renewal_date, vw.membersince, gs.Sic_Code
FROM vwCurrentCards AS vw LEFT OUTER JOIN
Trade_Goods_And_Services AS gs ON vw.Trade_ID = gs.Trade_ID AND gs.Sic_Code = 'MTA5'
My results are still 82 rows. I ran a series of queries to find out that in my current view none of them are BBB members so in theory, I should get 0 results.
Conversely, my query for non-BBB members:
SELECT vw.Trade_ID, vw.Account, vw.Business_Name, vw.cardfirst, vw.New_Card, vw.Date_Issued, vw.Renewal_date, vw.membersince, gs.Sic_Code
FROM vwCurrentCards AS vw LEFT OUTER JOIN
Trade_Goods_And_Services AS gs ON vw.Trade_ID = gs.Trade_ID AND gs.Sic_Code <> 'MTA5'
My results are still 427 rows. With the queries I ran, all 82 (original rows in the view) are non-BBB members so my results should be 0 rows.
Any suggestions?
Thanks,
John
November 30, 2010 at 12:10 pm
Move the bit about the SIC code to the Where clause instead of the Join clause. See if that does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 12:23 pm
That worked for the BBB members. I am still getting too many records for the non-BBB members.
November 30, 2010 at 12:33 pm
I'd have to see the tables and the data in them to tell you specifically what to do.
Take a look at the data returned, you'll need to see what doesn't make sense in it. Are you ending up with multiple copies of rows because of multiple rows in the sub-table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 12:44 pm
Yes, that is the case. For example, I queried just my rows from the sub table (Trade_Goods_And_Services) and I have 9 rows. When I look at the results from my non-BBB join, I have 9 rows. That is what prompted me to include the "<>" in the "ON" part of the query.
Thanks for your help so far!
John
November 30, 2010 at 12:50 pm
So, it seems to me that it's doing exactly what it should be. It's giving you the rows for the companies, and their data from the sub-table. Right?
If that's not what you want, can you provide at least a sample of what you're getting, and what you want to be getting?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 1:16 pm
I have 82 rows in my view. I need to now seperate which is BBB and which in non-BBB. The 82 rows is (for this) case FIXED, I need to create 82 cards. The sub-table (Trade_Goods_And_Services) is what will distinguish this, based on Sic_Code = 'MTA5', which is our indicator of BBB. So all this in mind the amount of rows in Query 1 (BBB) + Query 2 (non-BBB) should equal 82 (amount of rows in View). Presently Query 1 = zero rows (which is correct) however Query 2 = 427 rows, which is NOT correct because there are only 82 rows in my view (main table).
So, if the query is doing what it is suppose to do, then I have the wrong query. Which is why I came here to begin with :). My second query NEEDS TO/SHOULD (in this case) return 82 rows. I know this due to analyzing the data and removing duplicates in Excel. Which told me I have NO BBB members in this current View.
I am not sure if I mentioned this and it really has NO impact on the result, but here we go. The View is everyone who has a member card expiring with in the next 14 days. The process will run every 2 weeks. That being said, in 2 weeks, there may be 100 members in the View and who knows the BBB/non-BBB split.
December 1, 2010 at 6:22 am
Okay. I get it. What you need then is a column added to the view (either directly added to the view or in a query that includes the view) which indicates if there's a row in another table with a certain state.
Here's a sample of how to do that. You'll need to translate for your view & table.
select *,
case
when exists
(select 1
from dbo.MyOtherTable
where Col = 'MP55'
and EntityID = MyView.ID) then 'BBB'
else 'Not BBB'
end as BBB
from dbo.MyView;
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2010 at 9:37 am
That helps! I was actualy thinking this morning I should just find a way to include it in the View. Thanks for all your help!!
December 2, 2010 at 8:49 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply