April 29, 2016 at 9:09 am
Hello All,
Please see the table below:
Column A - Column B - Column C
----------- ------------- ------------
col1val1 -- col2val --- col3val1
col1val1 -- col2val --- col3val1
col1val1 -- col2val --- col3val1
col1val1 -- col2spec -- col3val2
col1val1 -- col2val --- col3spec
col1val2 -- col2val --- col3val2
col1val2 -- col2val --- col3spec
col1val2 -- col2val --- col3val3
I'm looking for a way grab all the records that have a specific value in Column A, Example: Column A = 'col1val1', IF at least one of those records has Column B = 'col2spec' and Column C = 'col3spec'. The data records I'm working with will never have a situation where one record will have both Column B = 'col2spec' and Column C = 'col3spec'.
In that data above, I want all of the records for Column A = 'col1val1' because one of those records has Column B = 'col2spec' and a different record (that still has Column A = 'col1val1') has Column C = 'col3spec'. I would not want any records that have Column A = 'col1val2'.
I can't figure out a way to do this with a query. I feel like I need to have some sort of script with looping and some variables. Is this the case? Can anyone point me in the right direction? I've been looking at this a bit on the internet but feel like I'm missing something to connect my ideas together.
April 29, 2016 at 9:20 am
I think I'm a bit confused on your requirements. If not, this is a pretty easy query. I've mocked up an example to show what I think you are asking for. If this is not correct, can you please tell me the exact results you want to see based on the sample data you've provided?
DECLARE @myTable TABLE (ColumnA varchar(15), ColumnB varchar(15), ColumnC varchar(15))
INSERT INTO @myTable
SELECT 'col1val1', 'col2val', 'col3val1' UNION ALL
SELECT 'col1val1', 'col2val', 'col3val1' UNION ALL
SELECT 'col1val1', 'col2val', 'col3val1' UNION ALL
SELECT 'col1val1', 'col2spec', 'col3val2' UNION ALL
SELECT 'col1val1', 'col2val', 'col3spec' UNION ALL
SELECT 'col1val2', 'col2val', 'col3val2' UNION ALL
SELECT 'col1val2', 'col2val', 'col3spec' UNION ALL
SELECT 'col1val2', 'col2val', 'col3val3'
SELECT *
FROM@myTable
WHEREColumnA = 'Col1val1'
AND (ColumnB = 'Col2spec'
OR ColumnC = 'Col3spec')
April 29, 2016 at 9:30 am
John Rowan (4/29/2016)
I think I'm a bit confused on your requirements. If not, this is a pretty easy query. I've mocked up an example to show what I think you are asking for. If this is not correct, can you please tell me the exact results you want to see based on the sample data you've provided?
DECLARE @myTable TABLE (ColumnA varchar(15), ColumnB varchar(15), ColumnC varchar(15))
INSERT INTO @myTable
SELECT 'col1val1', 'col2val', 'col3val1' UNION ALL
SELECT 'col1val1', 'col2val', 'col3val1' UNION ALL
SELECT 'col1val1', 'col2val', 'col3val1' UNION ALL
SELECT 'col1val1', 'col2spec', 'col3val2' UNION ALL
SELECT 'col1val1', 'col2val', 'col3spec' UNION ALL
SELECT 'col1val2', 'col2val', 'col3val2' UNION ALL
SELECT 'col1val2', 'col2val', 'col3spec' UNION ALL
SELECT 'col1val2', 'col2val', 'col3val3'
SELECT *
FROM@myTable
WHEREColumnA = 'Col1val1'
AND (ColumnB = 'Col2spec'
OR ColumnC = 'Col3spec')
Thanks for your help, John.
Would that select statement also return the below record?
col1val2, col2val, col3spec
What I want returned is the below records:
col1val1, col2val, col3val1
col1val1, col2val, col3val1
col1val1, col2val, col3val1
col1val1, col2spec, col3val2
col1val1, col2val, col3spec
I want ALL col1val1 records as long as at least one record has col2spec and one other record has col3spec. I even want the records for col1val that don't have either.
What does UNION ALL do?
April 29, 2016 at 9:47 am
OK, I think I understand you now. First off, the top part with the @myTable declaration and INSERT statement (with UNION ALL) is purely to set up an example of your data so we can test a query. Do you have a SQL Server instance where you can run this? If so, you'll see that my example, as-is, returns the following result set:
ColumnAColumnBColumnC
col1val1col2speccol3val2
col1val1col2valcol3spec
Based on your reply, this is not really what you want. You are saying that you want to find all rows where Column A = 'Col1val1' only if there is an occurrence of a combination of ColumnA = 'col1val1' AND ColumnB = 'col2spec' OR an occurrence of ColumnA= 'col1val1' AND ColumnC = 'col3spec'. Is this correct? If so, your new query becomes as follows:
DECLARE @myTable TABLE (ColumnA varchar(15), ColumnB varchar(15), ColumnC varchar(15))
INSERT INTO @myTable
SELECT 'col1val1', 'col2val', 'col3val1' UNION ALL
SELECT 'col1val1', 'col2val', 'col3val1' UNION ALL
SELECT 'col1val1', 'col2val', 'col3val1' UNION ALL
SELECT 'col1val1', 'col2spec', 'col3val2' UNION ALL
SELECT 'col1val1', 'col2val', 'col3spec' UNION ALL
SELECT 'col1val2', 'col2val', 'col3val2' UNION ALL
SELECT 'col1val2', 'col2val', 'col3spec' UNION ALL
SELECT 'col1val2', 'col2val', 'col3val3'
SELECT *
FROM@myTable t
INNER JOIN (
SELECTDISTINCT ColumnA
FROM@myTable
WHEREColumnA = 'Col1val1'
AND ColumnB = 'Col2spec'
UNION
SELECTDISTINCT ColumnA
FROM@myTable
WHEREColumnA = 'Col1val1'
AND ColumnC = 'Col3spec'
) t2 ON t.ColumnA = t2.ColumnA
May 2, 2016 at 10:12 am
Using HAVING eliminates a pass of the table and makes it easier to specify different conditions.
SELECT mt.*
FROM@myTable mt
INNER JOIN (
SELECT ColumnA
FROM @myTable
GROUP BY ColumnA
HAVING MAX(CASE WHEN ColumnB = 'Col2spec' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN ColumnC = 'Col3spec' THEN 1 ELSE 0 END) = 1
) AS mt_matches ON mt_matches.ColumnA = mt.ColumnA
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply