Finding the right JOIN type
-
Is this what you are looking for?
--Creating Tables
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) )
CREATE TABLE zzxpgrpr
(division varchar(3),
group_type varchar(1),
group_code varchar(9),
group_name varchar(20) )
CREATE TABLE zzxpgtyr
(group_type varchar(1),
type_name varchar(20),
pkey integer PRIMARY KEY )
--Inserting Sample Data
INSERT INTO zzxstylr
VALUES ('MDG','2204852','02', '', '', '', '', '', '01')
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'
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
--Query For Your Requirement
Select style, [LBL],
From
(Select a.style, c.type_name, b.group_name From zzxstylr As a
JOIN zzxpgrpr As b ON a.division = b.division
JOIN zzxpgtyr As c ON b.group_type = c.group_type) As d
Pivot
(Min(Group_Name) For type_name in ([LBL],
) ) As pvt
I hope this helped.
-
I'm not a pro in this area, but as I understand, «Min(Group_Name)» — isn't a the right rule that correctly determines which group corresponds to the style. As I understood, the right algorithm should compare the group_type and the group_code of that group, and select a corresponding group_name from zzxpgrpr as b.
Though, I think, you've used a very good practice by creating a simple correlational names for that tables with such a strange an non-human-readable names 🙂
And it was also a very interesting idea to use pivot relational operator. I wonder how I didn't get it, didn't hit upon that! I'll try to correct my version so that it also uses pivot now
-
Here's my old version that now has been renewed and that now uses aliases.
By the way, 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.
selecta.style,
lbl_b.group_name as lbl,
div_b.group_name as div
from
zzxstylr as a
join zzxpgtyr as lbl_c
on lbl_c.type_name='LBL'
/*or (on lbl_c.pkey=1) if you like*/
--determine the group_type corresponding to the group_col1 (='A')
join zzxpgtyr as div_c
on div_c.type_name='DIV'
/*or (on div_c.pkey=8) by analogy*/
--determine the group_type for group_col8 (='H')
join zzxpgrpr as lbl_b
on 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')*/
join zzxpgrpr as div_b
on div_b.group_type=div_c.group_type
and div_b.group_code=a.group_code8
/*and maybe (and a.division=div_b.division)*/
/*determine the group_name for the group_type of div and to the
group_code8 ('JUNIOR' corresponds to group_type='H' and
group_code='01')*/
where
(a.style = '2204852');
-
Anatoly Ukhvanov (6/11/2012)
I'm not a pro in this area, but as I understand, «Min(Group_Name)» — isn't a the rigth rule that correctly determines which group corresponds to the style. As I understood, the right algorithm should compare the group_type and the group_code of that group, and select a corresponding group_name from zzxpgrpr as b.Any Logic could be applied to get an expected Result Set.
But in your case there doesn't seem to be a fixed universal Logic.
You have 8 GroupCodes, which one should be compared with the Group Code in "zzxpgrpr"?
Due to this ambiguity in the Logic, I Joined the tables on Division and not on Group_Code.
Without a clear logic there could be many ways to get the Expected ResultSet but none of them would be Universal.
If you can work the exact logic according to which you want your result Set then it would be really helpful.
-
vinu512 (6/11/2012)
Any Logic could be applied to get an expected Result Set.But in your case there doesn't seem to be a fixed universal Logic.
Without a clear logic there could be many ways to get the Expected ResultSet but none of them would be Universal.
If you can work the exact logic according to which you want your result Set then it would be really helpful.
I think that I did get the idea of subject area's logic. I'll try to explain it for you, if I can.
vinu512 (6/11/2012)
You have 8 GroupCodes, which one should be compared with the Group Code in "zzxpgrpr"?Due to this ambiguity in the Logic, I Joined the tables on Division and not on Group_Code.
We should select from 'b' that row which has the same group_type as in 'c' and the same group_code as in 'a'.
-
vinu512 (6/11/2012)
Any Logic could be applied to get an expected Result Set.By the way, if any logic could be applied, why wouldn't you suggest the simplest solution:
select * from (values ('2204852','GOSSIP','JUNIOR')) as t(style,lbl,div):hehe: Do you really think ANY logic could be applied? 😀
-
And here's my version with use of pivot:
select style,lbl,divfrom (
select
a.style,
c.type_name,
b.group_name
from
zzxstylr as a
join zzxpgtyr as c
on c.type_name='LBL'
or c.type_name='DIV'
/*or (on c.pkey=1 or c.pkey=8) if you like*/
/*determine the group_type corresponding to the group_col1 (='A')
group_col8 (='H')*/
join zzxpgrpr as b
on
b.group_type=c.group_type
and(
(
c.type_name='LBL'
and b.group_code=a.group_code1
)or(
c.type_name='DIV'
and b.group_code=a.group_code8
)
)
/*and maybe (and a.division=b.division)*/
/*determine the group_name corresponding to the group_type
and to the group_code: if we perform the comparing to the 'div'-row
(in that case c.type_name='LBL') then we should compare the
group_code with group_code1, if with 'DIV' then group_code8*/
where
(a.style = '2204852')
)as t
pivot (
min(group_name)
for type_name
in (lbl,div)
) as pBut I must warn you: using pivot operator may constrain you in some cases. This is because pivot operator uses aggregate functions, so data in query result isn't updatable. That means that if you, for example, decide to create a view based on your query, like this:
create view pivot_query asselect style,lbl,div
from (
select
a.style,
c.type_name,
b.group_name
from
zzxstylr as a
join zzxpgtyr as c
on c.type_name='LBL'
or c.type_name='DIV'
/*or (on c.pkey=1 or c.pkey=8) if you like*/
/*determine the group_type corresponding to the group_col1 (='A')
group_col8 (='H')*/
join zzxpgrpr as b
on
b.group_type=c.group_type
and(
(
c.type_name='LBL'
and b.group_code=a.group_code1
)or(
c.type_name='DIV'
and b.group_code=a.group_code8
)
)
/*and maybe (and a.division=b.division)*/
/*determine the group_name corresponding to the group_type
and to the group_code: if we perform the comparing to the 'div'-row
(in that case c.type_name='LBL') then we should compare the
group_code with group_code1, if with 'DIV' then group_code8*/
where
(a.style = '2204852')
)as t
pivot (
min(group_name)
for type_name
in (lbl,div)
) as p
your view won't be updatable. While the following view (based on 4 joins instead of 2 joins and pivot in previous view) will be updatable (I've verified that!):
create view joins_query asselect
a.style,
lbl_b.group_name as lbl,
div_b.group_name as div
from
zzxstylr as a
join zzxpgtyr as lbl_c
on lbl_c.type_name='LBL'
/*or (on lbl_c.pkey=1) if you like*/
--determine the group_type corresponding to the group_col1 (='A')
join zzxpgtyr as div_c
on div_c.type_name='DIV'
/*or (on div_c.pkey=8) by analogy*/
--determine the group_type for group_col8 (='H')
join zzxpgrpr as lbl_b
on 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')*/
join zzxpgrpr as div_b
on div_b.group_type=div_c.group_type
and div_b.group_code=a.group_code8
/*and maybe (and a.division=div_b.division)*/
/*determine the group_name for the group_type of div and to the
group_code8 ('JUNIOR' corresponds to group_type='H' and
group_code='01')*/
where
(a.style = '2204852');
But don't try to update more than one column at a time, 'cause this'll cause updates in several source tables, so SSMS'll deny that. Though, you still can update one column at a time.
And the second (possible) constraint of pivot operator is that it uses derived table syntax, which is also often deprecated in some cases. For example, if you want to create an indexed view, you cannot use derived tables, nor CTEs in its definition. This is second (possible) reason why you shouldn't use query based on pivot operator but joins-based one instead.
-
Good grief, this thread reads like a traincrash.
Three sample tables are presented because they are all required. No pivoting is necessary. The business rules are a little nonstandard because the styles table contains columns which should be normalised out into a separate table. Here's a solution which, if not exactly correct, is close enough to be useful, and generates the correct results:
SELECT[STYLE]= s.style,
[LBL]= g1.Group_name,
= g8.Group_name
FROM #zzxstylr s
INNER JOIN #zzxpgrpr g1
ON g1.division = s.division AND g1.group_code = s.group_code1
INNER JOIN #zzxpgtyr t1 ON t1.group_type = g1.group_type AND t1.pkey = 1 -- LBL
INNER JOIN #zzxpgrpr g8
ON g8.division = s.division AND g8.group_code = s.group_code8
INNER JOIN #zzxpgtyr t8 ON t8.group_type = g8.group_type AND t8.pkey = 8 -- DIV
WHERE s.style = '2204852'
You can't code against tables without knowing the business rules. Sometimes you can guess - and that's exactly what this is - a guess.
“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/12/2012)
Good grief, this thread reads like a traincrash.Maybe it does, but if you take a look at my join-based solution (which I've offered above), you'll see that it doesn't differ from yours, except that myne is densely commented 🙂
selecta.style,
lbl_b.group_name as lbl,
div_b.group_name as div
from
zzxstylr as a
join zzxpgtyr as lbl_c
on lbl_c.type_name='LBL'
/*or (on lbl_c.pkey=1) if you like*/
--determine the group_type corresponding to the group_col1 (='A')
join zzxpgtyr as div_c
on div_c.type_name='DIV'
/*or (on div_c.pkey=8) by analogy*/
--determine the group_type for group_col8 (='H')
join zzxpgrpr as lbl_b
on 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')*/
join zzxpgrpr as div_b
on div_b.group_type=div_c.group_type
and div_b.group_code=a.group_code8
/*and maybe (and a.division=div_b.division)*/
/*determine the group_name for the group_type of div and to the
group_code8 ('JUNIOR' corresponds to group_type='H' and
group_code='01')*/
where
(a.style = '2204852');
By the way, the shortened form (without long names and any comments) is, of course, much easier-to-read, if you prefer minimalistic style of coding. But it's not so easy-to-understand, especially if you aren't a SQL Server guru, and you ask a question about how to choose the right type of join. 🙂
select a.style,b1.group_name lbl,b8.group_name divfrom zzxstylr a
join zzxpgtyr c1 on c1.pkey=1
join zzxpgtyr c8 on c8.pkey=8
join zzxpgrpr b1 on b1.group_type=c1.group_type and b1.group_code=a.group_code1
join zzxpgrpr b8 on b8.group_type=c8.group_type and b8.group_code=a.group_code8
where a.style=2204852;
-
You are right Grasshopper!
I am not an expert SQL server guru and I will provably never be one not matter how much time I spend on this, but I try! and that is why I put my question out here.
I appreciate the help!!!!
All I need to do is to incorporate with the rest of my existing query.
About the table names, I also agree with all of you. The naming sucks! and just like you (nicely) worked on my initial "garbage", I now need to work on someone else's.
I am greatly appreciated with your help and I am looking forward for the next question I have that I will hold one in case I figure it our along the way.
Once again, THANK YOU!!!!!
-
As you haven't posted the complete list of tables you have to work with and the full view of results you want to get (you've posted a simplified version), I've tried to write a query without having even a possibility to test it on my machine. So, if some mistypes will be found, you should correct them by yourself. Here's the query:
select zzoordrd.style,zzoordrh.customer,zzoordrh.division,zzoordrh.end_date,zzoordrh.ord_qty,zzoordrh.ord_status,zzoordrh.po_num,zzoordrh.pri_date,zzoordrh.ord_num,,zzxpgrpr_lbl.group_name lbl,zzxpgrpr_div.group_name div
from zzoordrh inner join
zzoordrd on zzoordrd.orn_num=zzoordrh.ord_num inner join
zzxstylr on zzoordrd.style=zzxstylr.style inner join
zzxpgrpr zzxpgrpr_lbl on zzxstylr.division=zzxpgrpr_lbl.division and zzxpgrpr_lbl.group_code=zzxstylr.group_code1 inner join
zzxpgtyr zzxpgtyr_lbl on zzxpgrpr_lbl.group_type=zzxpgtyr_lbl.group_type and zzxpgtyr_lbl.pkey=1 inner join
zzxpgrpr zzxpgrpr_div on zzxstylr.division=zzxpgrpr_div.division and zzxpgrpr_div.group_code=zzxstylr.group_code8 inner join
zzxpgtyr zzxpgtyr_div on zzxpgrpr_div.group_type=zzxpgtyr_div.group_type and zzxpgtyr_div.pkey=8
where zzoordrh.ord_status='O' and zzoordrh.ord_qty>0 and zzoordrh.customer='AQ32266' and zzoordrd.fkey/*pkey?*/=zzoordrh.pkey
order by zzoordrh.customer
P. S. I didn't understand from your previous post: do you prefer the queries suggested to you to be in shortened form (like I've posted above in this post) or in detailed one (like I proposed in my earlier posts)?
-
enling112010, are you reliableitservice?
-
Anatoly Ukhvanov (6/13/2012)
enling112010, are you reliableitservice?No, it's spam, reported.
“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/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» 😀
-
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 😎
“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
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply