November 17, 2016 at 7:08 am
Hopefully this isn't too daunting given that I'm providing a rather lengthy piece of sample code. I understand that cross joins using where clauses are the same as inner joins but I would rather display the code given to me as inner joins. A record can have up to ten descriptions and those need to be displayed all on one line rather than as ten separate rows which is why there are joins on the same tables multiple times. I can make the obvious joins below that only involve joining the table once FROM Table1 T1
INNER JOIN Table2 T2 ON T1.nbr = T2.nbr
INNER JOIN Table6 T6_1 ON T6_1.nbr = T1.nb2
INNER JOIN Table4 T4 ON T2.StID = T4.StID
INNER JOIN Table7 T7_1 ON T7_1.ID = T6_1.ID
INNER JOIN Table5 T5_1 ON T6_1.ID2 = T5_1.ID
INNER JOIN Table3 T3 ON T2.Code = T3.Code
My trouble comes when try to incorporate the multiple joins of Table5, Table6, and Table7. Here is the sample code in its entirety SELECT T6_1.DATE
,T3.Code
,T2.Nbr
,T2.NAME
,T4.STATE
,T2.DLR
,T1.nbr2
,T1.Type
,T5_1.[Description]
,T5_2.[Description]
,T5_3.[Description]
,T5_4.[Description]
,T5_5.[Description]
,T5_6.[Description]
,T5_7.[Description]
,T5_8.[Description]
,T5_9.[Description]
,T5_10.[Description]
FROM Table1 T1
,Table2 T2
,Table3 T3
,Table4 T4
,Table5 T5_1
,Table5 T5_2
,Table5 T5_3
,Table5 T5_4
,Table5 T5_5
,Table5 T5_6
,Table5 T5_7
,Table5 T5_8
,Table5 T5_9
,Table5 T5_10
,Table6 T6_1
,Table6 T6_2
,Table6 T6_3
,Table6 T6_4
,Table6 T6_5
,Table6 T6_6
,Table6 T6_7
,Table6 T6_8
,Table6 T6_9
,Table6 T6_10
,Table7 T7_1
,Table7 T7_2
,Table7 T7_3
,Table7 T7_4
,Table7 T7_5
,Table7 T7_6
,Table7 T7_7
,Table7 T7_8
,Table7 T7_9
,Table7 T7_10
WHERE T1.nbr = T2.nbr
AND T6_1.nbr = T1.nbr2
AND T2.StID = T4.StID
AND T7_1.ID = T6_1.ID
AND T7_2.ID = T6_2.ID
AND T7_3.ID = T6_3.ID
AND T7_4.ID = T6_4.ID
AND T7_5.ID = T6_5.ID
AND T7_6.ID = T6_6.ID
AND T7_7.ID = T6_7.ID
AND T7_8.ID = T6_8.ID
AND T7_9.ID = T6_9.ID
AND T7_10.ID = T6_10.ID
AND T6_1.ID2 = T5_1.ID
AND T6_2.ID2 = T5_2.ID
AND T6_3.ID2 = T5_3.ID
AND T6_4.ID2 = T5_4.ID
AND T6_5.ID2 = T5_5.ID
AND T6_6.ID2 = T5_6.ID
AND T6_7.ID2 = T5_7.ID
AND T6_8.ID2 = T5_8.ID
AND T6_9.ID2 = T5_9.ID
AND T6_10.ID2 = T5_10.ID
AND T2.Code = T3.Code
Is there any easy way to convert this to inner joins or possibly some other way to be able to display the description up to ten times on the same line without having to join the tables on themselves ten times?
November 17, 2016 at 7:13 am
Absolutely, but could you provide some sample data. I'd prefer to be able to verify any code is working as intended before tossing it out there.
Cheers,
November 17, 2016 at 7:40 am
You're missing WHERE clauses. You might also be taking a bad approach. You need to create a cross tabs query which would be more effective.
Here's an article that describe them
http://www.sqlservercentral.com/articles/T-SQL/63681/
And here's an example:
CREATE TABLE Table1(
id int,
RowNum int,
Descrip varchar(50)
);
INSERT INTO Table1
VALUES(1,1,'Description 1')
,(1,2,'Description 2')
,(1,3,'Description 3')
,(1,4,'Description 4')
,(1,5,'Description 5')
,(1,6,'Description 6')
,(2,1,'Description 1')
,(2,2,'Diff Descr 2')
,(2,3,'Diff Descr 3')
,(2,4,'Diff Descr 4')
,(2,5,'Diff Descr 5')
,(2,6,'Diff Descr 6');
SELECT ID,
MAX( CASE WHEN RowNum = 1 THEN Descrip END) AS Desc1,
MAX( CASE WHEN RowNum = 2 THEN Descrip END) AS Desc2,
MAX( CASE WHEN RowNum = 3 THEN Descrip END) AS Desc3,
MAX( CASE WHEN RowNum = 4 THEN Descrip END) AS Desc4,
MAX( CASE WHEN RowNum = 5 THEN Descrip END) AS Desc5,
MAX( CASE WHEN RowNum = 6 THEN Descrip END) AS Desc6
FROM Table1
GROUP BY id;
GO
DROP TABLE Table1;
November 17, 2016 at 7:50 am
Can't dig into it right now, but something tickles the back of my mind that you may be able to do this with just the CASE statement without the MAX/GROUP BY if you do a single JOIN with the proper clause.
Just give us CREATE TABLE statement with INSERTs for sample data and your expected output and we can come up with solutions to solve your needs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 17, 2016 at 8:41 am
Here's some sample data. There is some more filtering I need to do on my end so that is why it returns so many results. My intent was to get the syntax for joining the same tables multiple times.
create table Table1
(
nbrbigint null
,nbr2 bigintnull
,Type varchar(25) null
)
insert into Table1
select 1345, 20851901, 'Smith, Fred' union all
select 45234, 21192877, 'Jones, Tom' union all
select 7245, 21198688, 'Smith, Jessica' union all
select 642, 21115744, 'Wilson, Janet' union all
select 843, 34252125, 'Thomas, Drew'
create table Table2
(
nbrbigint null
,StID bigint null
,Name varchar(25) null
,DLRbigint null
,Code bigint null
)
insert into Table2
select 1345, 35, 'Test1' ,83593, 12 union all
select 45234, 23, 'Test2' ,94278, 13 union all
select 7245, 42, 'Test3' ,27849, 14 union all
select 3034, 11, 'Test4' ,27348, 15 union all
select 3037, 3, 'Test5' ,19232, 16
create table Table3
(
Code bigint null
)
insert into Table3
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16
create table Table4
(
StID bigint null
,State varchar(25) null
)
insert into Table4
select 35, 'California' union all
select 23, 'Alabama' union all
select 42, 'Florida' union all
select 12, 'Maine' union all
select 8, 'Arizona'
create table Table5
(
ID bigint null
,Description varchar(25) null
)
insert into Table5
select 317, 'Desc1' union all
select 346, 'Desc2' union all
select 352, 'Desc3' union all
select 376, 'Desc4' union all
select 292, 'Desc5'
create table Table6
(
nbr bigint null
,Date datetime null
,ID bigint null
,ID2 bigint null
)
insert into Table6
select 20851901, '11/17/2016', 1367782, 285 union all
select 21192877, '11/17/2016', 1367783, 248 union all
select 21198688, '11/16/2016', 1367708, 317 union all
select 23495283, '11/15/2016', 1367740, 346 union all
select 25672222, '11/17/2016', 1367806, 352
create table Table7
(
ID bigint null
)
insert into Table7
select 1367708 union all
select 1367740 union all
select 1367806 union all
select 1367302 union all
select 1366462
November 17, 2016 at 9:02 am
The sample data is no help. We don't have expected results and I'm not sure you know what to expect from that data.
November 23, 2016 at 9:47 am
RonMexico (11/17/2016)
My intent was to get the syntax for joining the same tables multiple times.
First, you have the table self-join syntax correct (tableA as A1, tableA as A2... Where A1.ID = A2.ID), but you're needing to revisit your join conditions and make sure you have them mapped properly. Once cleaned up, your sample data returns a single record * # of rows in T5 * # of rows in T6 * # of rows in T7. Basically, you see Jessica Smith 19k times with every possible combination of description in the ten columns, even if the description is not related.
Second, I highly recommend you move to the ANSI-92 style joins. IMO, it makes these large join statements much easier to work with as you can easily see if you've missed a join condition.
Third, your sample data is nearly useless for your problem. T5 and T6 only have one possible match (ID/ID2 = 317). The other values in T5 don't exist in T6. Solving your problem with only one matching description will be difficult. Ideally, provide some real data as a sample, but scrub the sensitive stuff.
Fourth, another problem with the data is there is no unique identifier for the description. Unless you truly want to repeat the same description value in 10 different columns, you'll need some way of identifying each description record. This may point to a fundamental design issue.
Fifth, as others have said, showing a sample of expected results is very helpful. This will allow us to identify business rules (what if there are only 9 descriptions? is the row excluded or is the final description null?) that will affect the script.
Finally, why do you need to see the description in 10 different columns? How will the columns be used? What business problem are you trying to resolve? The experts here have seen a lot of things. Providing the Why may allow them to point you in a better direction.
Wes
(A solid design is always preferable to a creative workaround)
November 23, 2016 at 12:03 pm
whenriksen -
The example I provided is code for a report we are taking over from another group. The first goal is to convert it to ANSI-92 style joins so that it is easier to read and we can get a better feel what we are dealing with. I apologize for the sample data not being of any help. I tried to alter the code and strip the data way down in order to eliminate any identifying information. The reason for needing to see the description in ten fields is because an account can have up to ten associated descriptions with it and the results need to be fanned out showing those ten on the same line rather than ten rows for each record. Perhaps there is an easier way to do that by joining the tables only once instead of joining these tables on themselves ten times as they did in the inherited code. I'm open to any suggestions.
November 23, 2016 at 12:15 pm
use Excel or some other spreadsheet tool and type up what your desired result will look like, with column headers. Than paste that back into the thread so we know what the target looks like.
When refactoring, make one change at a time and ensure your code is runnable AND returns the same result before moving on to the next change. Don't fix and refactor in one shot. It is too easy to change the logic by accident.
Wes
(A solid design is always preferable to a creative workaround)
November 23, 2016 at 12:31 pm
I'm away from a computer and will be for the next few days so I'll give it an attempt to type it out here. The headers will read:
Date, Code, Nbr, Name, State, DLR, Nbr2, Type, Description1, Description2, Description3, Description4, Description5, Description6, Description7, Description7, Description9, Description10.
Nbr is unique to each row and should be what all results are grouped by.
Each Nbr will have at least description1 and can have up to description10.
November 23, 2016 at 12:38 pm
RonMexico (11/23/2016)
whenriksen -The example I provided is code for a report we are taking over from another group. The first goal is to convert it to ANSI-92 style joins so that it is easier to read and we can get a better feel what we are dealing with. I apologize for the sample data not being of any help. I tried to alter the code and strip the data way down in order to eliminate any identifying information. The reason for needing to see the description in ten fields is because an account can have up to ten associated descriptions with it and the results need to be fanned out showing those ten on the same line rather than ten rows for each record. Perhaps there is an easier way to do that by joining the tables only once instead of joining these tables on themselves ten times as they did in the inherited code. I'm open to any suggestions.
If the descriptions are on different rows then you need to use the cross tab approach suggested by Luis.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 16, 2017 at 7:39 am
I'm bringing this topic back to life because the need for it has come back. Luckily, this time it is a more scaled back version and I figured out the code I need to display the results as expected. To provide a quick refresher, I can have an account that has multiple entries that displays like this:
Acct ID
-------- ----------
ABC 1
ABC 2
XYZ 1
XYZ 3
XYZ 9
I need the result to display like:
Acct ID1 ID2 ID3
-------- ------- --------- -------
ABC 1 2
XYZ 1 3 9
Here is my code for that;with CTE as
(
select T1.Column1
, T2.Column2
, T3.Column3
, row_number() over (partition by T1.Column1 order by T2.Column2) as Column4
from Database1.Schema1.Table1 T1
full join Database1.Schema1.Table2 T2 on T1.Column1 = T2.Column5
left join Database1.Schema1.Table3 T3 on T2.Column2 = T3.Column6
where T1.Column1 is not null
)
select Column1
, max(case when Column4 = 1 then Column2 else null end) as Column1a
, max(case when Column4 = 2 then Column2 else null end) as Column2a
, max(case when Column4 = 3 then Column2 else null end) as Column3a
, max(case when Column4 = 4 then Column2 else null end) as Column4a
, max(case when Column4 = 5 then Column2 else null end) as Column5a
, max(case when Column4 = 6 then Column2 else null end) as Column6a
, max(case when Column4 = 7 then Column2 else null end) as Column7a
, max(case when Column4 = 8 then Column2 else null end) as Column8a
, max(case when Column4 = 1 then Column3 else null end) as Column1b
, max(case when Column4 = 2 then Column3 else null end) as Column2b
, max(case when Column4 = 3 then Column3 else null end) as Column3b
, max(case when Column4 = 4 then Column3 else null end) as Column4b
, max(case when Column4 = 5 then Column3 else null end) as Column5b
, max(case when Column4 = 6 then Column3 else null end) as Column6b
, max(case when Column4 = 7 then Column3 else null end) as Column7b
, max(case when Column4 = 8 then Column3 else null end) as Column8b
from CTE
group by Column1
I'm completely blanking on my joins. There are 2,902,196 entries in Table1 and the query is only returning 2,902,181. I would expect them to match given that I need every record in Table1 to display regardless if they have an entry in Table2 and the entries are, also, flattened to display in one line in the case of Column1 existing more than once in Table2. Any suggestions on how to change my joins to achieve this?
March 16, 2017 at 7:55 am
I swear all it takes is to walk away for a minute and things make sense. It turns out Table1 has some duplicates in it. Let me clear them out and take another look at it before anyone wastes their time looking at my previous question. I'll come back if I'm still running into problems.
March 16, 2017 at 11:27 am
I took care of the duplicates in one table but now I'm having trouble joining tables with a one to many relationship. I realize this is getting off topic based on the original thread but I'm hoping it is an easy fix. Table A consists of accounts and Table B lists the changes to those accounts. I'm only interested in joining on the distinct changes made to each account. The problem is that an account can have duplicate change types if it happens to start as that type, be changed to a different type, and then changed back to the original type.
AccountID | ChangeID
-------------------------------------
123 | A
123 | B
123 | A
789 | C
I'm trying to do this but it's bringing back incorrect values based on a bad join
select a.AccountID, b.AccountID, b.ChangeID from Table1 a
left join (select AccountID, ChangeID from Table2 group by AccountID, ChangeID) t1 on a.AccountID = t1.AccountID
left join table2 b on b.ChangeID = T1.ChangeID
Any suggestions on what to change?
March 16, 2017 at 1:22 pm
RonMexico - Thursday, March 16, 2017 11:27 AMI took care of the duplicates in one table but now I'm having trouble joining tables with a one to many relationship. I realize this is getting off topic based on the original thread but I'm hoping it is an easy fix. Table A consists of accounts and Table B lists the changes to those accounts. I'm only interested in joining on the distinct changes made to each account. The problem is that an account can have duplicate change types if it happens to start as that type, be changed to a different type, and then changed back to the original type.AccountID | ChangeID
-------------------------------------
123 | A
123 | B
123 | A
789 | CI'm trying to do this but it's bringing back incorrect values based on a bad join
select a.AccountID, b.AccountID, b.ChangeID from Table1 a
left join (select AccountID, ChangeID from Table2 group by AccountID, ChangeID) t1 on a.AccountID = t1.AccountID
left join table2 b on b.ChangeID = T1.ChangeIDAny suggestions on what to change?
I'm in a time crunch now, but it sounds like you will need a correlation to restrict your changetypes table to be the max (or min) for each type for each account.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply