Finding the right JOIN type

Question

Finding the right JOIN type

reliableitservice

SSC Enthusiast

Points: 194
More actions
June 9, 2012 at 11:38 pm

#270840

Hi all,
I am finding difficulty deciding what is the best Join type.
I have three tables: "STYLES", "GRPUPREF", "GRPTYPE"
STYLE:
style,
group_code1, <- Let's call this Style Label "LBL"
group_code2,
group_code3,
group_code4,
group_code5,
group_code6,
group_code7,
group_code8, <- Let's call this Style Division "DIV"
GRPREF:
group_code, <- Group Code
group_name, <- Group Name
group_type, <-Style group type code (A-H from GRPTPYE Table below)
GRPTYPE:
group_type <- Style group type code (A-H)
PKEY <- Row ID or group code number (from style table)
There is one style "style1" that has a LBL (group_code1=02) and a DIV (group_code8=01"
In the GRPREF table:
group_code|group_name|group_type
02 |GOSSIP |A
02 |MISSY |H
GRPTYPE tbale:
grouyp_type|PKEY
A |1
H |8
I have so far used STYLE INNER JOIN GRPREF ON group_code1 = group_code
but I am getting seven records as a result. I should be getting one.
I need the results to be as follow:
STYLE |LBL |DIV
style1 |GOSSIP|MISSY
My issue is either the type of JOIN or the columns I'm joining them on, or maybe because I am not using the GRPTYPE table at all but the thing is that I don't know
how to integrate it with the other two.
Does anybody knows.
I am not sure if my explication is clear but I can provide more info if you willing to help.
Thank you so much!!!!!!!
RITS

Viewing 15 posts - 1 through 15 (of 54 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You have posted quite a bit of information, but unfortunately, I am not quite able to figure out what it is you are trying to accomplish. One thing that would really help is you would take the time to read the first article I have referenced below in my signature block. It will walk you through the what you need to post and how to post it to get the best possible answers. Also, be sure to post the results you expect to see frm the sample data you provide. This will give us something to test our code against. Put it the form of another table (call it ExpectedResults if you want) the same way you provide us with all the other information.

vinu512 SSCoach Points: 15729 More actions · Answer 2

I can understand that you need a JOIN.

But the rest of the parts are not very clear.

You need to give us a little more sample data for all the three tables.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Anatoly Ukhvanov SSC Enthusiast Points: 191 More actions · Answer 3

I'm not a professional in SQL, I'm a newbie.

But I think I could help… As I understand, you have this:

style_table:

style | group_code1 | … | group_code8

--------+-------------+---+-------------

style1 | 02 | … | 01

grpref_table:

group_code | group_name | …

------------+------------+---

02 | GOSSIP | …

01 | MISSY | …

[By the way, as I understand, you have mistyped in the last row: missy group_name has the code=01, not 02, right?]

and you need something like this as result:

result_dataset:

style | lbl | div

--------+--------+-------

style1 | GOSSIP | MISSY

If so, your problem is that you should use not one but two different joins (of the same type). You can achieve that with this code:

select

style_table.style as style,

grpref_table_lbl.group_name as lbl,

grpref_table_div.group_name as div

from

style_table inner join grpref_table as grpref_table_lbl

on style_table.group_code1=grpref_table_lbl.groupcode

inner join grpref_table as grpref_table_div

on style_table.group_code8=grpref_table_div.groupcode

I hope this will help you. But I must warn you: if you want to create a many-to-many relationship between tables style_table and grpref_table (so that one record in table style_table is related to many records in table grpref_table (in your case one record in style_table is related to [up to] 8 records in grpref_table) and one record in grpref_table is related to many records in style_table (in your case, as I understand, many styles can have the same group_code)), you should (according to relational theory) use 3 tables like this:

style_table:

style

--------

style1

grpref_table:

group_code | group_name | …

------------+------------+---

02 | GOSSIP | …

02 | MISSY | …

style_to_grpref_relation_table

style | group_code

--------+------------

style1 | 02

style1 | 01

etc…

and not to use 8 columns for each grpref assigned to that style. Imagine what you would do if there were, for example, not 8 but 60 group_codes per each style? Would you create 60 columns? 🙂

P.S. In my SSMS this code:

create database exampleDb;

go

use exampleDb

create table style_table(style varchar(50), group_code1 varchar(50), group_code8 varchar(50))

insert style_table values ('style1','02','01')

create table grpref_table(groupcode varchar(50), group_name varchar(50),grouptype varchar(50))

insert grpref_table values ('02','gossip','a'),('01','missy','h')

select

style_table.style as style,

grpref_table_lbl.group_name as lbl,

grpref_table_div.group_name as div

from

style_table inner join grpref_table as grpref_table_lbl

on style_table.group_code1=grpref_table_lbl.groupcode

inner join grpref_table as grpref_table_div

on style_table.group_code8=grpref_table_div.groupcode

returns this:

style1gossipmissy

P.P.S. My English isn't so good because it's not my native language. I'm a student from Ukraine, so Russian is my native language. 🙂

And where do you from and what language is your native one?

reliableitservice SSC Enthusiast Points: 194 More actions · Answer 4

Thank you all for your responses and I thought my post was going to be kind of complicated after I had posted.

Here, I attached something that could make this a bit more clear.

Hope this will help.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

reliableitservice (6/11/2012)
Thank you all for your responses and I thought my post was going to be kind of complicated after I had posted.
Here, I attached something that could make this a bit more clear.
Hope this will help.

What would really help to make this clear is to read the link Lynn suggested, or the first one in my signature. Once you post ddl, sample data and desired results you will get an answer pretty quickly.

_______________________________________________________________

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/

Anatoly Ukhvanov SSC Enthusiast Points: 191 More actions · Answer 6

I completely agree with Sean and Lynn: post, please, DDL-code (the definitions of your tables (including foreign key constraints)) and DML-code (sample data) and the results you want to have.

Moreover, you do already have DDL-code! (in SSMS in object browser choose in context item for desired tables choose item «Generate script for the table ? Using create statement» — this is your DDL-code!). Do you think we want to retype this code from screenshots you've posted (in Excel stylesheet)? It also would be nice if you delete from the code everything that doesn't consern your problem.

Your case is still compicated until you comply these demands.

P. S. Did I say something wrong? Does anyone disagree with me?

By the way, look at my previous post: I wrote that code manually — only to clarify what exactly your problem is. I thought it would help, but unfortunately, I misunderstood you. If you write DDL+DML, it would be most likely hard to misunderstand. 🙂

So, we're waiting for the code. 🙂

reliableitservice SSC Enthusiast Points: 194 More actions · Answer 7

Ok, sorry for that; it does make sense to put our post in a way that make sense.

I hope I am now in a better position here.

Based on the instructions, I am now creating my three tables and at the end is the code I am using where I am getting

seven records. I should be getting 1 (see below)

CREATE TABLE zzxstylr

(division FK varchar(3),style varchar(12),group_code1 varchar(9),group_code2 varchar(9),group_code3 varchar(9),

group_code4 varchar(9),group_code6 varchar(9),group_code7 varchar(9),group_code8 varchar(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 PK integer)

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

GO

Statement used:

SELECT zzxstylr.style, zzxstylr.group_code1, zzxpgrpr.group_name, zzxstylr.group_code8

FROM zzxstylr INNER JOIN zzxpgrpr ON zzxstylr.group_code1 = zzxpgrpr.group_code

WHERE (Zzxstylr.style = '2204852')

Results:

STYLEgroup_code1group_namegroup_code8

2204852 02 MISSY 01

2204852 02 GOSSIP 01

2204852 02 MISSY 01

2204852 02 GOSSIP 01

2204852 02 MISSY 01

DESIRED results

STYLELBLDIV

2204852 GOSSIP JUNIOR 01

I hope that I am now following the rules.

thank you

RITS

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

I am glad that you are seeing the benefit of providing consumable ddl and sample data. Unfortunately your create table statements don't work and you have a mismatched number of columns in your inserts.

It is important that what you post will actually work.

_______________________________________________________________

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/

Anatoly Ukhvanov SSC Enthusiast Points: 191 More actions · Answer 9

The code you've posted contains mistypes (it doesn't work in ssms). It means you didn't tested your code yourself. Would you like us to post unworking code too? 🙂

Anatoly Ukhvanov SSC Enthusiast Points: 191 More actions · Answer 10

CREATE TABLE zzxstylr

(division FK varchar(3),style varchar(12),group_code1 varchar(9),group_code2 varchar(9),group_code3 varchar(9),

group_code4 varchar(9),group_code5 varchar(9),group_code6 varchar(9),group_code7 varchar(9),group_code8 varchar(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 PK integer)

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

GO

Anatoly Ukhvanov SSC Enthusiast Points: 191 More actions · Answer 11

And these are results i have when executing your query:

It's only 3 lines here, not 8 like you wrote… Do I do something wrong?

Anatoly Ukhvanov SSC Enthusiast Points: 191 More actions · Answer 12

This is the solution of your problem:

Oops… 🙁 While editing this post I accidentally deleted all the text except sql-code. Though, it steel works.

select

zzxstylr.style,

lbl_zzxpgrpr.group_name as lbl,

div_zzxpgrpr.group_name as div

from

zzxstylr

join zzxpgtyr as lbl_zzxpgtyr

on lbl_zzxpgtyr.type_name='LBL'

/*or (on div_zzxpgtyr.pkey=1) if you like*/

--determine the group_type corresponding to the group_col1 (='A')

join zzxpgtyr as div_zzxpgtyr

on div_zzxpgtyr.type_name='DIV'

/*or (on div_zzxpgtyr.pkey=8) by analogy*/

--determine the group_type for group_col8 (='H')

join zzxpgrpr as lbl_zzxpgrpr

on lbl_zzxpgrpr.group_type=lbl_zzxpgtyr.group_type

and lbl_zzxpgrpr.group_code=zzxstylr.group_code1

/*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_zzxpgrpr

on div_zzxpgrpr.group_type=div_zzxpgtyr.group_type

and div_zzxpgrpr.group_code=zzxstylr.group_code8

/*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

(Zzxstylr.style = '2204852');

reliableitservice SSC Enthusiast Points: 194 More actions · Answer 13

You are all right, my bad. I should have test what I wrote for "miss spelling"

By creating the tables and using the code I am now having three records shown as result. I should still be getting one.

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

go

SELECT zzxstylr.style, zzxstylr.group_code1 AS LBL, zzxpgrpr.group_name, zzxstylr.group_code8 AS DIV

FROM zzxstylr INNER JOIN zzxpgrpr ON zzxstylr.group_code1 = zzxpgrpr.group_code

WHERE (Zzxstylr.style = '2204852')

STYLEgroup_code1group_namegroup_code8

2204852 02 GOSSIP 01

2204852 02 MISSY 01

DESIRED results

STYLELBLDIV

2204852 GOSSIP JUNIOR

I am truly sorry and I apologize for just jumping in and trow garbage without really looking.

The code above has been tested in SQL 2008.

Thank you in advance for your help!!!!!!!!!!!

Anatoly Ukhvanov SSC Enthusiast Points: 191 More actions · Answer 14

Check out my previous post, 'cause it seems that I've found a solution to your problem while you were correcting the mistypes 🙂

UPD! 'Check out my previous post'S! My solution works on your previous ddl! I did tests on previous ddl, the corrected form of which i've posted earlier (corrected by me)!

P.P.S. Though, it steel works with your new ddl too. sql server 2008 r2