March 3, 2014 at 5:49 pm
There are four tables
1. Matter
MID, CID, RType
001, a, m
002, a, m
003, b, m
004, c, m
2. Category
CID. RType
a, T
b, T
c, T
3. Security assignmnet
RID, RType, GID
001, m, g01
002, m, g01
002, m, g02
002, m, g03
003, m, g01
003, m, g03
a, T, g01
a, T, g02
a, T, g03
b, T, g02
b, T, g03
b, T, g04
4. Group
GID
g01
g02
g03
g04
I'd like to find the record in table #1 "Matter" which has exact record of "GID" in table #3 "Security Assignment" compare with table #2 "Category"
In this case, it is record of "002" bacause "002" in table#1 "Matter" and the record "a" in table #2 "category" both has exact GID records(g01, g02, g03) in table #3, "Security Assignment"
How can I create qury to find all the possible record in the table #2?
Thanks,
March 3, 2014 at 8:47 pm
Speaking of "Assignments", was this all the information your were given? There's no clear information provided that really links each table together. Are you sure you have all the necessary information given?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 4, 2014 at 10:02 am
You could do it either with inner joins or EXISTS clauses.
As this smells a bit like homework, implementation is left up to the reader.
March 4, 2014 at 12:50 pm
Thanks your both's responding!
The Matter has relationship with Category.
The goal it to find the record which the Matter and Category(this category related with the Matter) has exact the Group(s) assignment in Security Assignement table.
That's all the information I can think about. What's infomration would be needed additionally?
Can you provide me an example query syntax?
Thanks!
March 4, 2014 at 12:55 pm
Seattlemsp (3/4/2014)
Thanks your both's responding!The goal it to find the record which the Matter and Cateogry has exact the Group assignment.
That's all the information I can think about. What's infomration would be needed additionally?
Can you provide me an example query syntax?
Thanks!
Let's try a different approach here. The biggest issue on our side is we can't see you screen. You will need to post a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
Last but not least, this does seem an awful lot like homework. Around here people generally tend to not like posting answers for homework. If we do that you don't learn anything. Instead we like to see what you have tried and then we can help nudge you towards figuring out the solution.
_______________________________________________________________
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/
March 4, 2014 at 2:54 pm
First, thanks again all respondings!
Second this is not a homework from school. I greatly simplified the table structure. We have a security setup challenge. I need to find out all the possible records which has potiential security risk. Which is to find all the Matters which has exact group assignment with the Category to belongs to.
In belows case, the 494 record in Matter need to be found.
Here is the scirpt to create the tables
CREATE TABLE [dbo].[matter](
[matter_id] [int] IDENTITY(1,1) NOT NULL,
[category_id] [int] NOT NULL,
[r_type] [char](1) NOT NULL
CONSTRAINT [PK_matter] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[category](
[category_id] [int] IDENTITY(1,1) NOT NULL,
[r_type] [char](1) NOT NULL
CONSTRAINT [PK_category] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[group](
[group_id] [int] IDENTITY NOT NULL,
CONSTRAINT [PK_group] PRIMARY KEY NONCLUSTERED
CREATE TABLE [dbo].[security_assignment](
[record_id] [int] NOT NULL,
[group_id] [int] NOT NULL,
[record_type] [char](1) NOT NULL
CONSTRAINT [security_assignment] PRIMARY KEY CLUSTERED
Insert into table security_assignement
(
12363100021T
12363100073T
12363100381T
61100021M
61100381M
12406100013T
12406100016T
12406100017T
12406100018T
12406100029T
12406100030T
12406100031T
12406100045T
494100013M
494100016M
494100017M
494100018M
494100029M
494100030M
494100031M
494100045M
)
insert into table Matter
(
6112363M
49412406M
)
insert into table Category
(
12363T
12406T
)
Insert into table Group
(
100021
100073
100381
100013
100016
100017
100018
100029
100030
100031
100045
)
I greatly appreciate all the helps!
I would provide more info if needed.
March 4, 2014 at 3:32 pm
Seattlemsp (3/4/2014)
First, thanks again all respondings!Second this is not a homework from school. I greatly simplified the table structure. We have a security setup challenge. I need to find out all the possible records which has potiential security risk. Which is to find all the Matters which has exact group assignment with the Category to belongs to.
In belows case, the 494 record in Matter need to be found.
Here is the scirpt to create the tables
CREATE TABLE [dbo].[matter](
[matter_id] [int] IDENTITY(1,1) NOT NULL,
[category_id] [int] NOT NULL,
[r_type] [char](1) NOT NULL
CONSTRAINT [PK_matter] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[category](
[category_id] [int] IDENTITY(1,1) NOT NULL,
[r_type] [char](1) NOT NULL
CONSTRAINT [PK_category] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[group](
[group_id] [int] IDENTITY NOT NULL,
CONSTRAINT [PK_group] PRIMARY KEY NONCLUSTERED
CREATE TABLE [dbo].[security_assignment](
[record_id] [int] NOT NULL,
[group_id] [int] NOT NULL,
[record_type] [char](1) NOT NULL
CONSTRAINT [security_assignment] PRIMARY KEY CLUSTERED
Insert into table security_assignement
(
12363100021T
12363100073T
12363100381T
61100021M
61100381M
12406100013T
12406100016T
12406100017T
12406100018T
12406100029T
12406100030T
12406100031T
12406100045T
494100013M
494100016M
494100017M
494100018M
494100029M
494100030M
494100031M
494100045M
)
insert into table Matter
(
6112363M
49412406M
)
insert into table Category
(
12363T
12406T
)
Insert into table Group
(
100021
100073
100381
100013
100016
100017
100018
100029
100030
100031
100045
)
I greatly appreciate all the helps!
I would provide more info if needed.
Nice try on the ddl and sample data. Unfortunately none of it actually works. Your table definitions have syntax errors and the inserts aren't even close. The idea here is to make it easy for us to work on your problem instead of setting it up.
_______________________________________________________________
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/
March 4, 2014 at 3:39 pm
Yes good effort, but what we really need is a script we can run to set everything up with little to no effort. Everyone here is willing to help but I'm pretty sure we're also "on-the-job" at our own places of employment, so if it takes us a while to set everything up, chances are we'll "skip it" and move on to another post.
Try what you did with #temp tables and run it for yourself, if it work and all 4 tables have data, it will go a long way - and most likely you'll get an answer relatively quickly 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 6, 2014 at 1:10 pm
Sorry my lazy work and thank a lot the patient!
here is the script
CREATE TABLE [dbo].[matter](
[matter_id] [int] NOT NULL,
[category_id] [int] NOT NULL,
[r_type] [char](1) NOT NULL
)
insert into matter (matter_id, category_id, r_type)
values
(61, 12363, 'M'),
(494, 12406, 'M')
CREATE TABLE [dbo].[group](
[group_id] [int] NOT NULL
)
insert into [group] (group_id)
values
(100021),
(100073),
(100381),
(100013),
(100016),
(100017),
(100018),
(100029),
(100030),
(100031),
(100045)
CREATE TABLE [dbo].[security_assignment](
[record_id] [int] NOT NULL,
[group_id] [int] NOT NULL,
[r_type] [char](1) NOT NULL
)
insert into security_assignment (record_id, group_id, r_type)
values
(12363, 100021, 'T'),
(12363, 100073, 'T'),
(12363, 100381, 'T'),
(61, 100021, 'M'),
(61, 100381, 'M'),
(12406, 100013, 'T'),
(12406, 100016, 'T'),
(12406, 100017, 'T'),
(12406, 100018, 'T'),
(12406, 100029, 'T'),
(12406, 100030, 'T'),
(12406, 100031, 'T'),
(12406, 100045, 'T'),
(494, 100013, 'M'),
(494, 100016, 'M'),
(494, 100017, 'M'),
(494, 100018, 'M'),
(494, 100029, 'M'),
(494, 100030, 'M'),
(494, 100031, 'M'),
(494,100045, 'M')
CREATE TABLE [dbo].[category](
[category_id] [int] NOT NULL,
[r_type] [char](1) NOT NULL
)
insert into category (category_id, r_type)
values
(12363, 'T'),
(12406, 'T')
March 6, 2014 at 1:14 pm
OK we do have some working ddl and sample data. There is one VERY critical component still missing. What are you trying to do? Meaning, given your sample data what should the output be?
_______________________________________________________________
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/
March 6, 2014 at 3:58 pm
How can I create a qurey to find the record like 494 in security_assignement table. Bacasuse it has exactly records of group numbers as it asssociated Category(12406)
Matter 494 related with Category 12406
Both 494 and 12406 has exact groups assicoted with them
100013
100016
100017
100018
100029
100030
100031
100045
How can I pull 494 out from seurity_assignement table?
Thanks!
March 6, 2014 at 5:10 pm
How can I write a query to find out the record of 494 from Security_assignement table?
Bacause 494(Matter) has relatitionship with 12406(category), and both of them has exact groups associciate with them in Security_assignment table.
Thanks!
March 7, 2014 at 7:32 am
You need to understand that I have no idea what this data represents. You need to provide the business rules here not just some vague explanation about exact matches.
Why should you only get matter_id 494? 61 seems to have the same connection between these tables.
I don't know where to begin writing a query because I have no idea what you are looking for.
_______________________________________________________________
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/
March 7, 2014 at 9:58 am
Bacause from business operation, if the Matter and its assoicated Category assigned with exact groups, there is something wrong. That's why I need find all of those Matter(s).
It is not a common stright forward request. Not sure if I explain it clear enogh.
Thanks again,
March 7, 2014 at 10:12 am
How does the Group table factor in to this example?
Do you have an expected "exact" output of what the final result should be?
The best I can do (which I'm sure is not what you're looking for) is:SELECT m.matter_id, c.category_id, m.r_type, s.record_id
FROM #matter m
INNER JOIN #category c ON m.category_id = c.category_id
INNER JOIN #security_assignment s ON c.category_id = s.record_id AND c.r_type = s.r_type
WHERE m.matter_id = 494
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply