SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


compare records in tables


compare records in tables

Author
Message
Seattlemsp
Seattlemsp
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 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!
Seattlemsp
Seattlemsp
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 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!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)

Group: General Forum Members
Points: 125334 Visits: 18395
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.

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)
Seattlemsp
Seattlemsp
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 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,
MyDoggieJessie
MyDoggieJessie
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23000 Visits: 7462
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" ;-)
Seattlemsp
Seattlemsp
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 64
What I exect is something like

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

The the return is

494

Thanks!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)

Group: General Forum Members
Points: 125334 Visits: 18395
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 Modens 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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)

Group: General Forum Members
Points: 125334 Visits: 18395
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 Modens 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)
Seattlemsp
Seattlemsp
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search