Finding the right JOIN type
-
ChrisM@Work (6/13/2012)
Anatoly Ukhvanov (6/13/2012)
ChrisM@Work (6/13/2012)
No, it's spam, reported.Thanks, I'll «store it in my mind». 🙂
P. S. Did I choose the correct English idiom? Do American/English people say like this? (I'm from Ukraine, that's why I ask.)
N. B. In Russian when we want to say that we will remember smth. to apply it in the future, we often say: «I'll wind it round my moustache» 😀
Your local idiom is highly amusing - if I were you, I'd stick with it 😎
+1 That is hilarious!!!
In America we say "I will keep that in mind.". That is entirely too boring. I think I will use yours from now on, it should bring a chuckle or two.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
HI Grasshopper,
From the Post #1314836,
is this the rest of the tables I had posted at the beginning of the post from the attached?
The answer to the question: You can add details (note) just to see your logic but you don't have to.
I apply the query on the live database but I am still getting multiple results (9 actually).
I got 3860 records for the zzxpgrpr table and a total of 13 records that have a combination of
GROUP_CODE '01' and '02'
It worked in your server based on the few records I originally provided. If you add the one listed below,
you will get the same I am getting.
I hope it all make sense now.
INSERT INTO zzxpgrpr
(division, group_type,group_code,group_name)
SELECT 'BRD','A','02','GOOSIP'
UNION ALL
SELECT 'BRD','H','01','JUNIOR'
UNION ALL
SELECT 'QA','A','02','GOSSIP'
UNION ALL
SELECT 'BRD','H','02','MISSY'
UNION ALL
SELECT 'QA','H','01','JUNIOR'
UNION ALL
SELECT 'QA','H','02','MISSY'
UNION ALL
SELECT 'MDG','A','01','SINCE'
UNION ALL
SELECT 'QA','A','01','SINCE'
UNION ALL
SELECT 'BRD','A','01','SINCE'
-
reliableitservice (6/13/2012)
HI Grasshopper,reliableitservice, I'd like to notice that Grasshoper isn't my name, it's my «user level» on this forum. My name is Anatoly (page in Wikipedia). :hehe:
-
reliableitservice (6/13/2012)
From the Post #1314836,is this the rest of the tables I had posted at the beginning of the post from the attached?
Yes, it is.
reliableitservice (6/13/2012)
It worked in your server based on the few records I originally provided. If you add the one listed below,you will get the same I am getting.
It's probably because divisions also must be compared. Sinse it worked when there were only 3 records in zzxpgrpr table, and there was no need to compare divisions ('cause there was only one division in sample data), so I put the division comparison into comment (in my Post #1314202 that I've posted two days ago):
Anatoly Ukhvanov (6/11/2012)
pay attention to the comment where I suggest to compare not only group_type and group_code while selecting the appropriate group_name, but also take into account the equality of the division column in 'style' (which is simply 'a') and 'group' ('b') tables.join zzxpgrpr as lbl_bon lbl_b.group_type=lbl_c.group_type
and lbl_b.group_code=a.group_code1
/*and maybe (and a.division=lbl_b.division)*/
/*determine the group_name corresponding to the group_type of label
and to the group_code1 ('GOSSIP' corresponds to group_type='A'
and group_code='02')*/
I wrote comments not without purpose! 🙂 I've even payed your attention to that detail! Did you read that? 🙂
And by the way, Chris also proposed division comparison in Post #1314269:
ChrisM@Work (6/12/2012)
INNER JOIN #zzxpgrpr g1ON g1.division = s.division AND g1.group_code = s.group_code1
reliableitservice (6/13/2012)
It worked in your server based on the few records I originally provided. If you add the one listed below,you will get the same I am getting.
I've added «listed below», but the query below continues to work correctly returning a single record in the resultset:
Here's query's code you need:
selectzzxstylr.style,
zzxpgrpr_lbl.group_name lbl,
zzxpgrpr_div.group_name div
from
zzxstylr
join zzxpgrpr zzxpgrpr_lbl
on zzxstylr.division=zzxpgrpr_lbl.division
and zzxpgrpr_lbl.group_code=zzxstylr.group_code1
join zzxpgtyr zzxpgtyr_lbl
on zzxpgrpr_lbl.group_type=zzxpgtyr_lbl.group_type
and zzxpgtyr_lbl.pkey=1
join zzxpgrpr zzxpgrpr_div
on zzxstylr.division=zzxpgrpr_div.division
and zzxpgrpr_div.group_code=zzxstylr.group_code8
join zzxpgtyr zzxpgtyr_div
on zzxpgrpr_div.group_type=zzxpgtyr_div.group_type
and zzxpgtyr_div.pkey=8
where
zzxstylr.style=2204852
-
-- one method -------------------------------------------------------------SELECT
[STYLE]= s.style,
Attributes.*
FROM #zzxstylr s
OUTER APPLY (
SELECT
[LBL]= MAX(CASE WHEN [type_name] = 'LBL' THEN g.Group_name ELSE NULL END),
[PRINT] = MAX(CASE WHEN [type_name] = 'PRINT' THEN g.Group_name ELSE NULL END),
[TYPE]= MAX(CASE WHEN [type_name] = 'TYPE' THEN g.Group_name ELSE NULL END),
-- expand this list to include all rows in table zzxpgtyr
= MAX(CASE WHEN [type_name] = 'DIV' THEN g.Group_name ELSE NULL END)
FROM #zzxpgrpr g
INNER JOIN #zzxpgtyr t
ON t.group_type = g.group_type
WHERE g.division = s.division
AND g.group_code IN
(s.group_code1, s.group_code2, s.group_code3, s.group_code4, s.group_code6, s.group_code7, s.group_code8)
) Attributes
WHERE s.style = '2204852'
-- another method: run results into #temp table... ----------------------
SELECT
[STYLE]= s.style,
g.*
INTO #Temp
FROM #zzxstylr s
OUTER APPLY (
SELECT
t.[type_name], g.Group_name
FROM #zzxpgrpr g
INNER JOIN #zzxpgtyr t
ON t.group_type = g.group_type
WHERE g.division = s.division
AND g.group_code IN -- note: s.group_code5 is missing from DDL
(s.group_code1, s.group_code2, s.group_code3, s.group_code4, s.group_code6, s.group_code7, s.group_code8)
) g
WHERE s.style = '2204852'
-- generate query dynamically from the results...
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT Style'
SELECT @sql = @sql + ', ' + CHAR(10) + ' ['+[type_name]+'] = MAX(CASE WHEN [type_name] = '''+[type_name]+''' THEN Group_name ELSE NULL END)'
FROM #Temp
SET @sql = @sql + CHAR(10) + 'FROM #Temp' + CHAR(10) + 'GROUP BY Style'
-- check the resulting query for errors
PRINT @sql
-- and execute
EXEC(@SQL)
Enjoy.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
I didn't even understand what is it for? What does this code do?
For example, the first method returns this:
What is it? reliableitservice wrote (Post #1314145) he/she wants this as results:
reliableitservice (6/11/2012)
DESIRED resultsSTYLELBLDIV
2204852 GOSSIP JUNIOR
-
Anatoly Ukhvanov (6/14/2012)
I didn't even understand what is it for? What does this code do?For example, the first method returns this:
What is it? reliableitservice wrote (Post #1314145) he/she wants this as results:
reliableitservice (6/11/2012)
DESIRED resultsSTYLELBLDIV
2204852 GOSSIP JUNIOR
The styles table isn't normalised, best not to assume that there will only be two attributes (of 8 or 9 possible attributes) active against a style at any one time.
Both code samples I posted assume that a style may have all, some, or none of the attributes. I'm not yet sure that the OP is aware of the implications of this.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work (6/14/2012)
Anatoly Ukhvanov (6/14/2012)
I didn't even understand what is it for? What does this code do?For example, the first method returns this:
What is it? reliableitservice wrote (Post #1314145) he/she wants this as results:
reliableitservice (6/11/2012)
DESIRED resultsSTYLELBLDIV
2204852 GOSSIP JUNIOR
The styles table isn't normalised, best not to assume that there will only be two attributes (of 8 or 9 possible attributes) active against a style at any one time.
Both code samples I posted assume that a style may have all, some, or none of the attributes. I'm not yet sure that the OP is aware of the implications of this.
But your query returns wrong results! It should return this:
STYLELBLDIV2204852 GOSSIP JUNIOR
but it returns this instead:
STYLELBLDIV2204852 SINSE MISSY
Why?
-
Table zzxpgtyr has two columns which appear to have unique values,
group_type and pkey. Using both of these columns to join the table to the two other tables does result in eliminating a row from the result, but I'm not yet convinced that the join is correct.
Here's another way of getting the set of three results from the tables, this time normalising group_code on the fly:
SELECT s.division, s.style, x.GroupID, x.group_code, g.group_type, g.group_name, t.[type_name], t.pkeyFROM #zzxstylr s
CROSS APPLY (VALUES (1, group_code1),(2, group_code2),(3, group_code3),(4, group_code4), (5, ''), (6, group_code6), (7, group_code7), (8, group_code8)) x (GroupID, group_code)
INNER JOIN #zzxpgrpr g
ON g.division = s.division
AND g.group_code = x.group_code
INNER JOIN #zzxpgtyr t
ON t.group_type = g.group_type
-- AND t.pkey = x.GroupID
WHERE x.group_code <> ''
- which shows more clearly what I mean. If group_code1 corresponds to 'LBL' in the styles table, why isn't it called [LBL] instead of [group_code1]?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Nevertheless, I still don't understand, what are you trying to do?
I agree with you that in this example it looks strange: why they didn't call 'group_codeN' columns as 'lbl', 'print', … , 'div'. Or, at least, 'group_A', 'group_B', … , 'group_H'? But maybe those 'type_name's change sometimes, from time to time? Maybe therefore they didn't?
But I don't understand: why? Your first query Post #1314269 returned correct result: single-row resultset:
2204852 GOSSIP JUNIORWHY do you want to change something?And also I cannot understand the following. You wrote: «Here's another way of getting the set of three results from the tables…» — but your query returns 4 results instead of three. While reliableitservice asks you to write the query which returns a single (neither 4, nor 3) row in the resultset! What do you want to do? What are you trying to achieve?
-
Anatoly Ukhvanov (6/14/2012)
Nevertheless, I still don't understand, what are you trying to do?I agree with you that in this example it looks strange: why they didn't call 'group_codeN' columns as 'lbl', 'print', … , 'div'. Or, at least, 'group_A', 'group_B', … , 'group_H'? But maybe those 'type_name's change sometimes, from time to time? Maybe therefore they didn't?
But I don't understand: why? Your first query Post #1314269 returned correct result: single-row resultset:
2204852 GOSSIP JUNIORWHY do you want to change something?And also I cannot understand the following. You wrote: «Here's another way of getting the set of three results from the tables…» — but your query returns 4 results instead of three. While reliableitservice asks you to write the query which returns a single (neither 4, nor 3) row in the resultset! What do you want to do? What are you trying to achieve?
What I'm hoping to do is encourage the OP to find out for sure how these tables are supposed to relate to each other. There are plenty of ways to get the results out, but without the rules, simply returning the desired results isn't enough. If we filter on ptyp or whatever the integer pk is called, then one of the rows in the link table becomes redundant - it can never be used.
As it stands, we don't know if the dupe is caused by crap data or crap design - which means that any "correctly performing query" has been obtained by accident and may well not work with a different set of parameters.
My query returns 3 rows 😉
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
By the way, I'd like to pay attention on one interesting detail:
there is no need to look into the zzxpgtyr table to determine, which group_type corresponds to a given pkey. If a always corresponds to 1, b — to 2 etc, then we could use the following function to determine group_type: char(ascii('A')-1+pkey). Using this trick we can avoid reading from zzxpgtyr table. Here's the query which uses this trick and don't even refer to zzxpgtyr table:
selectzzxstylr.style,
zzxpgrpr_lbl.group_name lbl,
zzxpgrpr_div.group_name div
from
zzxstylr
join zzxpgrpr zzxpgrpr_lbl
on zzxstylr.division=zzxpgrpr_lbl.division
and zzxpgrpr_lbl.group_code=zzxstylr.group_code1
and zzxpgrpr_lbl.group_type=char(ascii('A')-1+1)
--you may replace the expression char(ascii('A')-1+1) to simply 'A'
join zzxpgrpr zzxpgrpr_div
on zzxstylr.division=zzxpgrpr_div.division
and zzxpgrpr_div.group_code=zzxstylr.group_code8
and zzxpgrpr_div.group_type=char(ascii('A')-1+8)--or simply 'H'
where
zzxstylr.style=2204852This approach may save 2 joins for us 🙂
This query returns the same resultset as the privious query I posted. But it works only if in the zzxpgtyr table group_type='A' always corresponds to pkey=1, B to 2, C to 3 etc!
-
ChrisM@Work (6/14/2012)
My query returns 3 rows 😉The following code contains 4 sections:
--in this section I create an example database
--this is code posted by OP in Post #1314145
--this is additional code posted by OP in Post #1315584
--and this is your code from Post #1315838, but I removed '#' characters
--in this section I create an example databasecreate database example
go
use example
--------------------------------------------------------------------------------
--this is code posted by OP in Post #1314145
CREATE TABLE zzxstylr
(division char(3) PRIMARY KEY,style char(12),group_code1 char(9),group_code2 char(9),group_code3 char(9),group_code4 char(9),group_code6 char(9),
group_code7 char(9),group_code8 char(9))
INSERT INTO zzxstylr
VALUES ('MDG','2204852','02', '', '', '', '', '', '01')
CREATE TABLE zzxpgrpr
(division varchar(3), group_type varchar(1),group_code varchar(9),group_name varchar(20))
INSERT INTO zzxpgrpr
(division, group_type,group_code,group_name)
SELECT 'MDG','H','01','JUNIOR'
UNION ALL
SELECT 'MDG','A','02','GOSSIP'
UNION ALL
SELECT 'MDG','H','02','MISSY'
UNION ALL
SELECT 'DOT','H','02','MISSY'
CREATE TABLE zzxpgtyr
(group_type varchar(1),type_name varchar(20),pkey integer PRIMARY KEY)
INSERT INTO zzxpgtyr
(group_type,type_name,pkey)
SELECT 'A','LBL',1
UNION ALL
SELECT 'B','PRINT',2
UNION ALL
SELECT 'C','TYPE',3
UNION ALL
SELECT 'D','TOP',4
UNION ALL
SELECT 'E','BOTTOM',5
UNION ALL
SELECT 'F','3rd PC',6
UNION ALL
SELECT 'G','CUST',7
UNION ALL
SELECT 'H','DIV',8
--------------------------------------------------------------------------------
--this is additional code posted by OP in Post #1315584
INSERT INTO zzxpgrpr
(division, group_type,group_code,group_name)
SELECT 'BRD','A','02','GOOSIP'
UNION ALL
SELECT 'BRD','H','01','JUNIOR'
UNION ALL
SELECT 'QA','A','02','GOSSIP'
UNION ALL
SELECT 'BRD','H','02','MISSY'
UNION ALL
SELECT 'QA','H','01','JUNIOR'
UNION ALL
SELECT 'QA','H','02','MISSY'
UNION ALL
SELECT 'MDG','A','01','SINCE'
UNION ALL
SELECT 'QA','A','01','SINCE'
UNION ALL
SELECT 'BRD','A','01','SINCE'
--------------------------------------------------------------------------------
--and this is your code from Post #1315838, but I removed '#' characters
SELECT s.division, s.style, x.GroupID, x.group_code, g.group_type, g.group_name, t.[type_name], t.pkey
FROM zzxstylr s
CROSS APPLY (VALUES (1, group_code1),(2, group_code2),(3, group_code3),(4, group_code4), (5, ''), (6, group_code6), (7, group_code7), (8, group_code8)) x (GroupID, group_code)
INNER JOIN zzxpgrpr g
ON g.division = s.division
AND g.group_code = x.group_code
INNER JOIN zzxpgtyr t
ON t.group_type = g.group_type
-- AND t.pkey = x.GroupID
WHERE x.group_code <> ''
Try to run this code and tell me, please, how much rows do you get. Me, I get 4 rows. Here's proofing screenshot:
-
Different sample data. I replaced the old data set with the new, and you appended the new.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
OK. But OP wrote that the new data should be appended: «If you add the one listed below,
you will get the same I am getting.»
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply