July 8, 2014 at 5:11 pm
I have a table that have different groups in it, All the records that belong to one Group have the same GroupID and each GroupID has same columns.
I need a query to generate the output in the attachment.
When the user input one GroupID then all the records belong to that group will pull out,
When the user input two GroupIDs then the user need to select another set of parameter (duplicate records or different records or nonoverlap records) to display
intersect of the two group or different records or except recors
when user input three GroupIDs then the user need to display the overlaps among all three groups or different among three groups or dinstinct records among the three groups
Of course user can input other filters.
Anyone has a smart way than using a whole bunch of "if...else"
Thank you!
July 8, 2014 at 8:58 pm
Really not enough information to help you. If you can post the DDL (CREATE TABLE statement) for the table(s) involved, sample data (INSERT INTO statements) that are representative of the problem domain (not live data), and the various expected outputs based on the different inputs based on the sample data would really help. Please remember that we can't see what you see so we need you to provide as much information as possible.
Take the time to read the first article I reference below in my signature block. It will walk you through the things we need you to post and how to post it.
July 9, 2014 at 3:50 pm
Thank you for the feedback! Lynn
Here's the simplified version of my table Structure:
GroupID col1 col2 col3 col4
1 as er v null
2 as er v null
1 df gh null v/
2 df gh bn m
2 vf fg c b
1 fg gh c b
Between GoupID 1 and 2
The output for "Only records that are equal" will be:
GroupId col1 col2 col3 col4
1 as er v null
' 2 as er v null' can't be shown since all the fields other than the GroupID are duplicates.
The output for "Only Records that are different" will be:
GroupId col1 col2 col3 col4
1 df gh null v/
2 df gh bn m
Good to have feature will be mark which column the difference is from, and he difference will always comes from either col3 or col4
The output for "Only records not exist in all Groups will be:
GroupId col1 col2 col3 col4
2 vf fg c b
1 fg gh c b
From above you can see that col1 and col2 combined to determine one record, the difference comes from either col3 or col4 between two GroupID
the output has to show all 5 columns.
The query needs to be able to compare three or more GroupIDs.
I would be really appreciate if anyone can come up an efficient query for the bottom two outputs. I was able to do the first one.
Thank you!
Here's my query for the first output:
Select min(GroupID) as GroupID, col1,col2,col3,col4
from
(Select GroupID, col1, col2, col3. col4
from table
where GroupID = 1
Union all
Select GroupID, col1, col2, col3. col4
from table
where GroupId = 2)
Group by col1, col2, col3, col4
Having count(*) > 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply