November 7, 2017 at 9:37 pm
hello all,
I have 3 tables. employee , division and empDivMap.
The employee table is the master table of employee data. The division table holds mapping of division and its sub divisions. A division can have one or more sub divisions. empDivMap table stores employee and its associated division. an employee can be associated with only one division.
I need to write a sql query such that it gives me the emp, div, sub division info as per the conditions below:
1) one record per employee
2 ) if there is only subdivision, return that emp-div-subdiv mapping
3) if there are more than one sub divisions, return emp-div with topmost record of the subdivision.
Thanks
November 8, 2017 at 1:56 am
When posting questions for T-SQL, you normally need to supply DDL and Consumable Sample data. This gives you a much better chance of others answering your questions, as they don't need to build your data (or in this case guess it), and can test their answers. There's a link in my signature on how to post that.
Secondly, this question sounds like a homework/exam question. What answer have you tried to you problem? As well as posting your DDL and Sample data, please also post what you've tried so far; so that we can point out where you might have gone wrong and what you should be looking at to get the results you want.
Lastly, is the 3 requirements 3 different data sets, or do they need to be returned as 3 separate ones?
Edit: Fourthly, are you really using SQL Server 2017?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 8, 2017 at 4:58 am
Welcome to the board Jyoti
Also, if you could provide a meaningful title to the post thread that helps others to scan for questions that they can assist with or that may contain solutions to their problems. The fact that you are posting a new thread infers that you need help 🙂
November 8, 2017 at 5:57 am
When you post the DDL for the tables like Thom requested, please include any foreign keys present.
Also, it sounds like the divisions table might contain a self-referential key used to build a tree of divisions and subdivisions. Is that the case?
Lastly, what determines which division row is the "topmost record of the subdivision" you described?
November 8, 2017 at 10:00 am
Ed Wagner - Wednesday, November 8, 2017 5:57 AMWhen you post the DDL for the tables like Thom requested, please include any foreign keys present.Also, it sounds like the divisions table might contain a self-referential key used to build a tree of divisions and subdivisions. Is that the case?
Lastly, what determines which division row is the "topmost record of the subdivision" you described?
Sorry about the ambiguity. The DDL for the tables are as follows:
create table employee(empid int, empname varchar(50))
create table empDivMap(empid int constraint empfk references employee(empid), divid int constraint divfk references division(divid))
create table division(divid int, subdiv int)
And there is no self-referential key in the division table. It is just one level. A division will have one or more subdivision. The subdivision have no hierarchy under them.
And by the 'topmost row of the subdivision' I meant, the first occurring subdivision record in the table. For instance,
div subdiv
~~~~~~~~~~~~
1 A
1 B
The subdiv 'A' occurs first in the table for div 1.
Thanks.
November 8, 2017 at 10:02 am
aaron.reese - Wednesday, November 8, 2017 4:58 AMWelcome to the board JyotiAlso, if you could provide a meaningful title to the post thread that helps others to scan for questions that they can assist with or that may contain solutions to their problems. The fact that you are posting a new thread infers that you need help 🙂
I agree to you and I apologize for it. I was in a bit of hurry when I posted the question and could not think of a suitable title then.
November 8, 2017 at 10:05 am
jyoti.raghuvanshi - Wednesday, November 8, 2017 10:00 AMEd Wagner - Wednesday, November 8, 2017 5:57 AMWhen you post the DDL for the tables like Thom requested, please include any foreign keys present.Also, it sounds like the divisions table might contain a self-referential key used to build a tree of divisions and subdivisions. Is that the case?
Lastly, what determines which division row is the "topmost record of the subdivision" you described?Sorry about the ambiguity. The DDL for the tables are as follows:
create table employee(empid int, empname varchar(50))
create table empDivMap(empid int constraint empfk references employee(empid), divid int constraint divfk references division(divid))
create table division(divid int, subdiv int)And there is no self-referential key in the division table. It is just one level. A division will have one or more subdivision. The subdivision have no hierarchy under them.
And by the 'topmost row of the subdivision' I meant, the first occurring subdivision record in the table. For instance,
div subdiv
~~~~~~~~~~~~
1 A
1 B
The subdiv 'A' occurs first in the table for div 1.Thanks.
For all the employees, I need their corresponding division and subdivision
November 8, 2017 at 10:06 am
jyoti.raghuvanshi - Wednesday, November 8, 2017 10:00 AMSorry about the ambiguity. The DDL for the tables are as follows:
create table employee(empid int, empname varchar(50))
create table empDivMap(empid int constraint empfk references employee(empid), divid int constraint divfk references division(divid))
create table division(divid int, subdiv int)And there is no self-referential key in the division table. It is just one level. A division will have one or more subdivision. The subdivision have no hierarchy under them.
And by the 'topmost row of the subdivision' I meant, the first occurring subdivision record in the table. For instance,
div subdiv
~~~~~~~~~~~~
1 A
1 B
The subdiv 'A' occurs first in the table for div 1.Thanks.
OK, we have DDL, good. HOw about that Sample data? There were also a few more questions after that as well.
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 8, 2017 at 12:00 pm
jyoti.raghuvanshi - Wednesday, November 8, 2017 10:00 AMThe DDL for the tables are as follows:
create table employee(empid int, empname varchar(50))
create table empDivMap(empid int constraint empfk references employee(empid), divid int constraint divfk references division(divid))
create table division(divid int, subdiv int)And there is no self-referential key in the division table. It is just one level. A division will have one or more subdivision. The subdivision have no hierarchy under them.
And by the 'topmost row of the subdivision' I meant, the first occurring subdivision record in the table. For instance,
div subdiv
~~~~~~~~~~~~
1 A
1 B
The subdiv 'A' occurs first in the table for div 1.Thanks.
Maybe I'm missing something, but if divid is a primary key of division, and there is no self-referential hierarchy in the division table, and there are only 2 columns in the table, how can a division have multiple sub-divisions? Your sample data here shows 2 rows for Div 1, but that shouldn't be possible given the DDL right?
November 9, 2017 at 5:55 am
jyoti.raghuvanshi - Wednesday, November 8, 2017 10:00 AMEd Wagner - Wednesday, November 8, 2017 5:57 AMWhen you post the DDL for the tables like Thom requested, please include any foreign keys present.Also, it sounds like the divisions table might contain a self-referential key used to build a tree of divisions and subdivisions. Is that the case?
Lastly, what determines which division row is the "topmost record of the subdivision" you described?Sorry about the ambiguity. The DDL for the tables are as follows:
create table employee(empid int, empname varchar(50))
create table empDivMap(empid int constraint empfk references employee(empid), divid int constraint divfk references division(divid))
create table division(divid int, subdiv int)And there is no self-referential key in the division table. It is just one level. A division will have one or more subdivision. The subdivision have no hierarchy under them.
And by the 'topmost row of the subdivision' I meant, the first occurring subdivision record in the table. For instance,
div subdiv
~~~~~~~~~~~~
1 A
1 B
The subdiv 'A' occurs first in the table for div 1.Thanks.
How do you define "first occurring record"? Without a sort order, there is no concept of "first" in SQL Server tables.
And I'll chime in too, what query have you tried so far?
November 9, 2017 at 6:01 am
Chris Harshman - Wednesday, November 8, 2017 12:00 PMjyoti.raghuvanshi - Wednesday, November 8, 2017 10:00 AMThe DDL for the tables are as follows:
create table employee(empid int, empname varchar(50))
create table empDivMap(empid int constraint empfk references employee(empid), divid int constraint divfk references division(divid))
create table division(divid int, subdiv int)And there is no self-referential key in the division table. It is just one level. A division will have one or more subdivision. The subdivision have no hierarchy under them.
And by the 'topmost row of the subdivision' I meant, the first occurring subdivision record in the table. For instance,
div subdiv
~~~~~~~~~~~~
1 A
1 B
The subdiv 'A' occurs first in the table for div 1.Thanks.
Maybe I'm missing something, but if divid is a primary key of division, and there is no self-referential hierarchy in the division table, and there are only 2 columns in the table, how can a division have multiple sub-divisions? Your sample data here shows 2 rows for Div 1, but that shouldn't be possible given the DDL right?
If we're honest Chris, that data can't even go in the relevant table. The OP has defined their table, division, as having 2 int columns, however, the values supplied (A and B) are not integers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 9, 2017 at 7:05 am
Thom A - Thursday, November 9, 2017 6:01 AMIf we're honest Chris, that data can't even go in the relevant table. The OP has defined their table, division, as having 2 int columns, however, the values supplied (A and B) are not integers.
Well, I noticed that too. I just thought it was more important at this point to understand if the table structures posted were accurate, since the sample data provided seemed to contradict that divid was a primary key of the division table.
November 9, 2017 at 7:06 am
Chris Harshman - Thursday, November 9, 2017 7:05 AMThom A - Thursday, November 9, 2017 6:01 AMIf we're honest Chris, that data can't even go in the relevant table. The OP has defined their table, division, as having 2 int columns, however, the values supplied (A and B) are not integers.Well, I noticed that too. I just thought it was more important at this point to understand if the table structures posted were accurate, since the sample data provided seemed to contradict that divid was a primary key of the division table.
I think we're missing a lot of pieces of the puzzle here. That or we're being fed very very wrong information. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply