select parent/ child records in same table

Question

select parent/ child records in same table

itortu

SSCommitted

Points: 1914
More actions
August 2, 2016 at 1:14 pm

#312268

I need to be able to select all instruments with their respective parent instrument, and category and show it in same row.
Example:
instrument_iddescriptionmeta_instrument meta_instrument_descriptionsub_category_id sub_category_description
2 guitar30string 930 guitar frettted strings
I am attaching a sample of the data, and a create table script. Hopefully someone can offer some help in creating this recursive join.
Many thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Your sample data doesn't seem to correspond with your expected results. Are you missing tables?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

As posted your sample data is not in a format that is consumable. Here is the table and data in a format that is readily consumable.

CREATE TABLE [dbo].[instrument_ref](

[instrument_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[description] [nvarchar](100) NOT NULL,

[sub_category_of] [int] NULL,

[meta_instrument] [int] NULL,

CONSTRAINT [PK_instrument_ref] PRIMARY KEY CLUSTERED

(

[instrument_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

insert instrument_ref

select 'Bass Saxophone', 350, 42 union all

select 'Guitar', 930, 30 union all

select 'Acoustic Guitar', 2, 30 union all

select 'Balalaika', 930, 30 union all

select 'Banjo', 930, 30 union all

select 'Electric Guitar', 2, 30 union all

select 'Bass Guitar', 2, 30 union all

select 'Rhythm Guitar', 2, 30 union all

select 'Ukulele', 930, 30 union all

select 'Keyboard', 11, 11 union all

select 'Clavichord', 11, 11 union all

select 'Harpsichord', 11, 11 union all

select 'Organ', 11, 11 union all

select 'Pipe Organ', 14, 11 union all

select 'Electronic Organ', 14, 11 union all

select 'Piano', 11, 11 union all

select 'Percussion', 18, 18 union all

select 'Bass Drum', 325, 18 union all

select 'Castenets', 18, 18 union all

select 'Celesta', 11, 11 union all

select 'Chimes', 96, 18 union all

select 'Cymbals', 18, 18 union all

select 'Glockenspiel', 96, 18 union all

select 'Gong', 18, 18 union all

select 'Snare Drum', 325, 18 union all

select 'Timpani', 325, 18 union all

select 'Xylophone', 96, 18 union all

select 'String', 30, 30 union all

select 'Cello', 136, 30 union all

select 'Double Bass', 136, 30 union all

select 'Viola', 136, 30 union all

select 'Violin', 136, 30 union all

select 'Voice', 35, 35 union all

select 'Alto Voice', 35, 35 union all

select 'Baritone Voice', 35, 35 union all

select 'Bass Voice', 35, 35 union all

select 'Soprano Voice', 35, 35 union all

select 'Tenor Voice', 35, 35 union all

select 'Contralto Voice', 35, 35 union all

select 'Woodwinds', 42, 42 union all

select 'Brass', 43, 43 union all

select 'Bb Euphonium', 493, 43 union all

select 'Bugle', 931, 43 union all

select 'Cornet', 931, 43 union all

select 'Double B-flat Baritone', 576, 43 union all

select 'Double B-flat Tuba', 53, 43 union all

select 'French Horn', 43, 43 union all

select 'Sousaphone', 53, 43 union all

select 'Trombone', 43, 43 union all

select 'Trumpet', 931, 43

_______________________________________________________________

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/

itortu SSCommitted Points: 1914 More actions · Answer 3

I uploaded a new file with more data.

Also this is the query I was starting to work myself:

SELECT DISTINCT i1.instrument_id

, i1.description [instrument_description]

, i2.instrument_id parent_instrument_id

, i2.description parent_instrument_description

, i3.instrument_id sub_category_id

, i3.description sub_category_description

FROM

instrument_ref i1

INNER JOIN instrument_ref i2

ON i1.meta_instrument = i2.instrument_id

INNER JOIN instrument_ref i3

ON i1.sub_category_of = i3.instrument_id

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

Luis Cazares (8/2/2016)
Your sample data doesn't seem to correspond with your expected results. Are you missing tables?

Yeah I suspect there is a sub-category table involved here that we don't have. If you exclude that last column this looks like a typical self join to me.

_______________________________________________________________

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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

I'm sorry, I didn't realized that the id values had gaps.

Here's a script that might help you and others for future references.

CREATE TABLE [dbo].[instrument_ref](

[instrument_id] [int] NOT NULL,

[description] [nvarchar](100) NOT NULL,

[sub_category_of] [int] NULL,

[meta_instrument] [int] NULL,

CONSTRAINT [PK_instrument_ref] PRIMARY KEY CLUSTERED

(

[instrument_id] ASC

));

INSERT INTO instrument_ref

VALUES

('1','Bass Saxophone','350','42'),

('2','Guitar','930','30'),

('3','Acoustic Guitar','2','30'),

('4','Balalaika','930','30'),

('5','Banjo','930','30'),

('6','Electric Guitar','2','30'),

('7','Bass Guitar','2','30'),

('8','Rhythm Guitar','2','30'),

('10','Ukulele','930','30'),

('11','Keyboard','11','11'),

('12','Clavichord','11','11'),

('13','Harpsichord','11','11'),

('14','Organ','11','11'),

('15','Pipe Organ','14','11'),

('16','Electronic Organ','14','11'),

('17','Piano','11','11'),

('18','Percussion','18','18'),

('19','Bass Drum','325','18'),

('20','Castenets','18','18'),

('21','Celesta','11','11'),

('22','Chimes','96','18'),

('23','Cymbals','18','18'),

('24','Glockenspiel','96','18'),

('25','Gong','18','18'),

('27','Snare Drum','325','18'),

('28','Timpani','325','18'),

('29','Xylophone','96','18'),

('30','String','30','30'),

('31','Cello','136','30'),

('32','Double Bass','136','30'),

('33','Viola','136','30'),

('34','Violin','136','30'),

('35','Voice','35','35'),

('36','Alto Voice','35','35'),

('37','Baritone Voice','35','35'),

('38','Bass Voice','35','35'),

('39','Soprano Voice','35','35'),

('40','Tenor Voice','35','35'),

('41','Contralto Voice','35','35'),

('42','Woodwinds','42','42'),

('43','Brass','43','43'),

('44','Bb Euphonium','493','43'),

('45','Bugle','931','43'),

('46','Cornet','931','43'),

('47','Double B-flat Baritone','576','43'),

('48','Double B-flat Tuba','53','43'),

('49','French Horn','43','43'),

('50','Sousaphone','53','43'),

('51','Trombone','43','43'),

('52','Trumpet','931','43');

SELECT i.instrument_id,

i.description,

m.instrument_id AS meta_instrument,

m.description AS meta_instrument_description,

c.instrument_id AS sub_category_id,

c.description AS sub_category_description

FROM instrument_ref i

JOIN instrument_ref m ON i.meta_instrument = m.instrument_id

LEFT

JOIN instrument_ref c ON i.sub_category_of = c.instrument_id

WHERE i.instrument_id <> i.meta_instrument;

GO

DROP TABLE [instrument_ref];

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

Aside the sub-category which isn't present in your sample data I think something as simple as this might be what you are looking for.

select *

from instrument_ref i

join instrument_ref d on d.sub_category_of = i.meta_instrument

where i.sub_category_of <> i.meta_instrument

_______________________________________________________________

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/

itortu SSCommitted Points: 1914 More actions · Answer 7

The sub_category_column is contained in the same table. No other table is missing.

For example, column sub_category_of with value of 930, it belongs to "Guitar/Fretted Strings", which also has its own sub_category_of value of 882, which belongs to "Plucked Strings".

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

itortu (8/2/2016)
The sub_category_column is contained in the same table. No other table is missing.
For example, column sub_category_of with value of 930, it belongs to "Guitar/Fretted Strings", which also has its own sub_category_of value of 882, which belongs to "Plucked Strings".

There is no value of "Guitar/Fretted Strings" anywhere in your sample data. Well it appears to be there in the new version. But the new version of your file is still user hostile. Can you turn that into insert statements?

_______________________________________________________________

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/

itortu SSCommitted Points: 1914 More actions · Answer 9

Hi Sean,

I uploaded a file with more data.

Look at file: instruments_reference_table.txt

Search for: 930Guitar/Fretted Strings88230

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

itortu (8/2/2016)
Hi Sean,
I uploaded a file with more data.
Look at file: instruments_reference_table.txt
Search for: 930Guitar/Fretted Strings88230

Yes and in my last post...I just don't have the energy to turn that into something usable having done it already with the first attempt.

Well it appears to be there in the new version. But the new version of your file is still user hostile. Can you turn that into insert statements?

_______________________________________________________________

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/