March 30, 2010 at 6:24 pm
Previous posts have given me a very nicely populated many-to-many linked table, but now I am running into a problem creating a view from these 3 tables. Here are the tables:
Rates ( RatesID int IDENTITY,
ClassCode varchar,
State varchar,
Territory varchar,
Carrier varchar)
Forms (FormsID int IDENTITY,
FormName varchar,
FormDescr varchar)
Rates_Forms(RatesID int, FormsID int)
And I have this view which is giving me every ClassCode for a specific State, Territory and Carrier, along with any FormNames related to that ClassCode.
SELECT Rates.ClassCode, Classes.ClassDescription, Forms.FormName, Forms.FormDescription, States.StateName,
Rates.Territory
FROM Classes INNER JOIN
Rates ON Classes.ClassCode = Rates.ClassCode
INNER JOIN
Places.States ON Rates.State = States.StateID
INNER JOIN
Carriers ON Rates.CarrierID = Carriers.CarrierID
INNER JOIN
Forms ON Carriers.CarrierID = Forms.CarrierID AND Carriers.CarrierID = Forms.CarrierID
INNER JOIN
Rates_Forms ON Rates.ClassRatesID = Rates_Forms.ClassRatesID AND Forms.FormsID =
Rates_Forms.FormsID AND Forms.FormsID = Rates_Forms.FormsID AND Forms.FormsID =
Rates_Forms.FormsID
However, this view is giving me ONLY those ClassCodes that have one or more FormNames related to it. I'd also like the view to list all the other ClassCodes that do not have a FormName related to it. If I could figure out how to query only those without a related FormName, I could just UNION the two. Should I be looking at using EXCEPT?
March 30, 2010 at 6:46 pm
Have you tried using a left outer join for this query in lieu of the inner joins?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2010 at 6:57 pm
I have tried that, yes. However, what that gets me is all 189 FormNames for each ClassCode. Many of the ClassCodes have no assigned forms, so I'd like it to show FormName as NULL.
I'm starting to think that my problem might be in my linked table. When I populated that table, it only inserted a row if a ClassCode had an assigned FormName. If the ClassCode had NULL for a FormName, it did not insert a row into the linked table.
My original thought was to create a new query that would return all ClassCodes that do NOT have a row in Rates_Forms and just UNION that with my current view in the original post. Or do I need to insert new rows into the linked table that point to a NULL FormName?
April 1, 2010 at 9:30 pm
I have tried that, yes. However, what that gets me is all 189 FormNames for each ClassCode.
Sounds like either a cross join or maybe a full join. The outer join should give you full results from one table and results where they exist from the second.
I'm starting to think that my problem might be in my linked table.
Definitely not. The linking table is as it should be.
If you are having problems with complex queries, start small and simple. Just join a couple of the tables and make sure you are getting the results back that you want. Then build on the additional joins and see how they affect your results.
Your first comment was that you wanted all Class Codes and all associated forms. If they don't have an associated form then you want the form name to be null.
Select R.ClassCode, F.FormName, F.FormDescr
From Rates As R
Left Join Rates_Forms As RF On RF.RatesId = R.RatesId
Left Join Forms As F On F.FormsId = RF.FormsId;
Go
The query you put up has a few consistency issues.
1. It includes several tables that you didn't list in your table definitions.
2. There are issues with field name consistency (Forms.FormDescr in table def; Forms.FormDescription in query) also (both your Rates and Forms tables join on CarrierId in the query; neither have that field in the table def... and only one of them should have it, not both).
3. The references don't match (INNER JOIN Places.States ON Rates.State = States.StateID).
4. There is repetition on your join fields (AND Forms.FormsID = Rates_Forms.FormsID AND Forms.FormsID = Rates_Forms.FormsID AND Forms.FormsID = Rates_Forms.FormsID).
As you build on additional joins, make sure you are using outer joins, as CirquedeSQLeil suggested. Would be glad to try and help out if you can post the complete set of table definitions so we get the full picture. Best of luck.
April 2, 2010 at 10:02 am
Thank you very much. I was getting so confused trying to figure out LEFT OUTER JOINS and RIGHT INNER JOINS... I was about to go JOIN a loony cult.
And sorry about the inconsistencies with my original post, I had copied in from a view I had created using SSMS. I need to learn to write out the code, I know.
This worked great and I've been able to add in the other columns I needed in the view. Thanks again for the help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply