April 20, 2006 at 7:03 am
I have about 13 tables that I'm trying to connect to a yr_mth table. All the tables have an eff_dte that is linked to the yr_mth table. That's all that the 13 tables have in common is the eff_dte. I get data back but it's coming like for instance, table has 7 rows of data, It'll take up seven rows of data for whole view vertically putting null in all the excess space it took up. And it does it for all the tables columns I bring in. Any suggestions?
April 20, 2006 at 8:07 am
What data do you have? (What are the table structures?) What data do you want back?
Show us a sample expected output.
April 20, 2006 at 8:13 am
Can you post the schema definition, some sample data for this issue ?
Is EFF_DTE a datetime/smalldatetime data-type column ? If so, then it has to "exactly" match in all those tables...
April 20, 2006 at 8:53 am
MTH_YR
ID int
MTH_YR varchar
TBL_ACH
ID int
RPC_ID int
EFF_DTE varchar
all other columns are varchar.
April 20, 2006 at 9:10 am
So what's the linkage? RPC_ID to ID or Mth_YR to Eff_DTE or what?
What's the expected output?
April 20, 2006 at 10:56 am
Mth_YR to Eff_DTE
April 20, 2006 at 11:07 am
Without expected output, I have to guess.
Your query should look something like:
select t.col1, m.col2
from
TBL_ACH t
INNER JOIN
MTH_YR m
ON
t.EFF_DTE = m.MTH_YR
Some notes:
Having the linkage be a varchar column is a risky proposition. There's just too much chance for things to not quite exactly match.
Generally, one uses the same (or at least similar) name for columns in tables that are linked. That way it's known (or at least suspected) that they're the same.
April 20, 2006 at 11:57 am
I agree with what you're saying. I think I'm going to have to create alot of views and link them together to get the output I'm looking for. I mean create views with MTH_YR and the data table. Then link those views into another view. What are your thoughts on this?
April 20, 2006 at 12:24 pm
It truly all depends on what it is that you're wanting to see.
For example, you might be able to work with the union command.
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp)
April 21, 2006 at 5:36 am
Ephriam,
I have tried to find out what you need, but I failed. I agree with Pam, that you need to explain more about the situation and required results, if we are to give any useful suggestions.
Do the tables have more things in common than just the eff_date? To use UNION, as Pam suggested, structures of the tables (that is, of those columns that you want to show) have to be identical - which does not mean that the columns must have the same name, but they must have the same data type ... and also should have some common meaning, otherwise it makes little sense to return them as one column.
If the only thing tables have in common is eff_date - as your first post seems to suggest - then I think an explanation would be in order WHY do you want to join them in a view. What purposes will the view serve? How will it be used? What type of data is in these tables - are these contracts, sales or what? We need something that would allow us to understand how it should work.
Just an offhand attempt to describe how something similar could look - maybe explaining what is different in your model will help to get the info:
CREATE TABLE #mth_yr([ID] int identity, mth_yr varchar(6))
CREATE TABLE #tbl_ach([ID] int identity, rpc_id int, eff_dte varchar(6), somecol varchar(100))
CREATE TABLE #tbl_qwe([ID] int identity, rpc_id int, eff_dte varchar(6), othercol varchar(100))
INSERT INTO #mth_yr (mth_yr) values ('200511')
INSERT INTO #mth_yr (mth_yr) values ('200512')
INSERT INTO #mth_yr (mth_yr) values ('200601')
INSERT INTO #mth_yr (mth_yr) values ('200602')
INSERT INTO #mth_yr (mth_yr) values ('200603')
INSERT INTO #mth_yr (mth_yr) values ('200604')
INSERT INTO #tbl_ach (rpc_id, eff_dte, somecol) values (1, '200512','Christmas special')
INSERT INTO #tbl_ach (rpc_id, eff_dte, somecol) values (1, '200602','Springtime is coming')
INSERT INTO #tbl_ach (rpc_id, eff_dte, somecol) values (2, '200511','Better buy a warm coat')
INSERT INTO #tbl_ach (rpc_id, eff_dte, somecol) values (3, '200603','First sunny day')
INSERT INTO #tbl_qwe (rpc_id, eff_dte, othercol) values (1, '200512','Santa Claus Inc.')
INSERT INTO #tbl_qwe (rpc_id, eff_dte, othercol) values (1, '200601','Freeze Co.Ltd.')
INSERT INTO #tbl_qwe (rpc_id, eff_dte, othercol) values (3, '200511','Autumn Fashion')
INSERT INTO #tbl_qwe (rpc_id, eff_dte, othercol) values (3, '200512','Santa Claus Inc.')
/*using "select *" is bad practice in a permanent select, but here it helps to analyze what is happening; be sure to replace it by column list later*/
SELECT *
FROM #mth_yr m
LEFT JOIN #tbl_ach a ON a.eff_dte = m.mth_yr
LEFT JOIN #tbl_qwe q ON q.eff_dte = m.mth_yr
ORDER BY m.mth_yr
As you see, because the only thing used to link tables is eff_date, and there can be several rows with the same eff_date in any of the tables, rows in your resultset start multiplying, and lots of columns have NULL values. Depending on what is the structure and required result, you can deal with it in several ways. You can use several columns for joining, use union to "merge" columns somecol and othercol, use CASE statement, make several queries instead of one and somehow manipulate the results to get what you need.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply