January 28, 2017 at 7:39 pm
Hello to All!
I have been doing a lot of my own SQL over the years and have had a lot of success, but this latest puzzle is a bit beyond my grasp.
Here is data from 2 tables as an example. Col1 in each table has data that relates to the other.
table a table b
col1 col2 ---- col1 col 2
1 a 1 10
1 a 1 11
1 a 1 12
2 b 2 11
2 b 2 12
3 c 3 10
3 c 3 11
3 c 3 12
Looking for a query that will return a row from these 2 tables where it will return a single row from table a for whenever the joined record from table b is missing a value in col2.
For the example above, the query would display the following:
table a
col1 col2
2 b
I have tried various things - various conditions of different joins, sub queries, etc.
I feel like I am very close but still missing the mark.
Any help is appreciated.
Alan
January 28, 2017 at 9:08 pm
Killian85 - Saturday, January 28, 2017 7:39 PMHello to All!I have been doing a lot of my own SQL over the years and have had a lot of success, but this latest puzzle is a bit beyond my grasp.
Here is data from 2 tables as an example. Col1 in each table has data that relates to the other.
table a table b
col1 col2 ---- col1 col 2
1 a 1 10
1 a 1 11
1 a 1 12
2 b 2 11
2 b 2 12
3 c 3 10
3 c 3 11
3 c 3 12Looking for a query that will return a row from these 2 tables where it will return a single row from table a for whenever the joined record from table b is missing a value in col2.
For the example above, the query would display the following:
table a
col1 col2
2 bI have tried various things - various conditions of different joins, sub queries, etc.
I feel like I am very close but still missing the mark.
Any help is appreciated.
Alan
What determines what the range of values for table b col 2 is? Assuming that the range of possible values is identified by a value's existence in tableb col 2, this code should do the trick:
declare @tablea table (
col1 int
, col2 char(1)
)
declare @tableb table (
col1 int
, col2 int
)
insert into @tablea(col1, col2)
select 1, 'a'
union all select 2, 'b'
union all select 3, 'c'
insert into @tableb(col1, col2)
select 1, 10
union all select 1, 11
union all select 1, 12
union all select 2, 11
union all select 2, 12
union all select 3, 10
union all select 3, 11
union all select 3, 12
select *
from @tablea as tablea
where exists (
select tableb.col2
from @tableb as tableb
where not exists (
select matching_tableb.col1
from @tableb as matching_tableb
where matching_tableb.col1 = tablea.col1
and matching_tableb.col2 = tableb.col2
)
)
Andrew P.
January 29, 2017 at 8:46 am
Andrew P - Saturday, January 28, 2017 9:08 PMKillian85 - Saturday, January 28, 2017 7:39 PMHello to All!I have been doing a lot of my own SQL over the years and have had a lot of success, but this latest puzzle is a bit beyond my grasp.
Here is data from 2 tables as an example. Col1 in each table has data that relates to the other.
table a table b
col1 col2 ---- col1 col 2
1 a 1 10
1 a 1 11
1 a 1 12
2 b 2 11
2 b 2 12
3 c 3 10
3 c 3 11
3 c 3 12Looking for a query that will return a row from these 2 tables where it will return a single row from table a for whenever the joined record from table b is missing a value in col2.
For the example above, the query would display the following:
table a
col1 col2
2 bI have tried various things - various conditions of different joins, sub queries, etc.
I feel like I am very close but still missing the mark.
Any help is appreciated.
AlanWhat determines what the range of values for table b col 2 is? Assuming that the range of possible values is identified by a value's existence in tableb col 2, this code should do the trick:
declare @tablea table (
col1 int
, col2 char(1)
)declare @tableb table (
col1 int
, col2 int
)insert into @tablea(col1, col2)
select 1, 'a'
union all select 2, 'b'
union all select 3, 'c'insert into @tableb(col1, col2)
select 1, 10
union all select 1, 11
union all select 1, 12
union all select 2, 11
union all select 2, 12
union all select 3, 10
union all select 3, 11
union all select 3, 12select *
from @tablea as tablea
where exists (
select tableb.col2
from @tableb as tableb
where not exists (
select matching_tableb.col1
from @tableb as matching_tableb
where matching_tableb.col1 = tablea.col1
and matching_tableb.col2 = tableb.col2
)
)
Andrew P.
Andrew, thank you for your insight into this.
Funny thing was, I was considering the use of a union and dismissed it out of hand.
Thank you again.
Alan A.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply