Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

compare records in tables Expand / Collapse
Author
Message
Posted Thursday, March 6, 2014 3:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:06 PM
Points: 32, Visits: 64
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!
Post #1548523
Posted Thursday, March 6, 2014 5:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:06 PM
Points: 32, Visits: 64
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!
Post #1548537
Posted Friday, March 7, 2014 7:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 14,584, Visits: 14,357
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1548741
Posted Friday, March 7, 2014 9:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:06 PM
Points: 32, Visits: 64
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,
Post #1548798
Posted Friday, March 7, 2014 10:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:27 AM
Points: 4,370, Visits: 7,227
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; They'll drag you down to their level and beat you with experience"
Post #1548806
Posted Friday, March 7, 2014 2:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:06 PM
Points: 32, Visits: 64
What I exect is something like

Select record_id
from Security_assignement
Join....
where ........

The the return is

494

Thanks!
Post #1548911
Posted Friday, March 7, 2014 3:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 14,584, Visits: 14,357
Seattlemsp (3/7/2014)
What I exect is something like

Select record_id
from Security_assignement
Join....
where ........

The the return is

494

Thanks!


It is crystal clear except for the portions where you have a series of dots and the logic required to get the output.

As I said previously why only 494 and not 61??? It seems you have some logic in your head that you are not sharing with us. We are trying to help but you are not trying to help us.

Perhaps the article referenced here will help.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1548914
Posted Friday, March 7, 2014 3:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 14,584, Visits: 14,357
The closest I can come up with is something like this.

select * 
from security_assignment sa
join matter m on m.r_type = sa.r_type
join [group] g on g.group_id = sa.group_id
join category c on c.category_id = m.category_id and m.r_type = sa.r_type

This returns both 61 and 494 for all the values that match but obviously is not totally correct as the matter gets mixed into it. Unless you can explain this in a way that makes sense I am going to leave this to somebody else.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1548915
Posted Tuesday, March 11, 2014 4:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:06 PM
Points: 32, Visits: 64
It is pretty close!

The reason for 61 not support to return bacause:

1. The M 61 does have two groups which are exect the same as it assoicated catgory
2. Hower the C 12363 has additional group of 100073 assigned to it but the M 61 doesn't have that group.

Not sure if this task is possible.

I apploigize of my slow responding. It has been a crazy time.

Many thanks your helpes!

Tianhong
Post #1549967
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse