I'm trying to create a query to find missing values in a table.
Example table:
COL1 COL2
---- ----
A 1
A 2
A 3
A 4
B 1
B 3
C 2
C 4
I want my output to be:
COL1 COL2
---- ----
B 2
B 4
C 1
C 3
jimnl69@hotmail.com wrote:
> I'm trying to create a query to find missing values in a table.
> Example table:
>
> COL1 COL2
> ---- ----
> A 1
> A 2
> A 3
> A 4
> B 1
> B 3
> C 2
> C 4
>
>
> I want my output to be:
>
> COL1 COL2
> ---- ----
> B 2
> B 4
> C 1
> C 3
>
On May 4, 9:43 am, lark
> jimn...@hotmail.com wrote:
> > I'm trying to create a query to find missing values in a table.
> > Example table:
> >
> > COL1 COL2
> > ---- ----
> > A 1
> > A 2
> > A 3
> > A 4
> > B 1
> > B 3
> > C 2
> > C 4
>
> > I want my output to be:
>
> > COL1 COL2
> > ---- ----
> > B 2
> > B 4
> > C 1
> > C 3
> >
> > I thought I could do this with some outer joins but I can't seem to
> > get even close. Any suggestions? Thanks!
>
> what are all the potential values that col2 can have?
>
> --
> lark -- ham...@sbcdeglobalspam.net
> To reply to me directly, delete "despam".
Whatever the unique values that exist in column 2 are.
jimnl69@hotmail.com wrote:
> On May 4, 9:43 am, lark
>> jimn...@hotmail.com wrote:
>>> I'm trying to create a query to find missing values in a table.
>>> Example table:
>>>
>>> COL1 COL2
>>> ---- ----
>>> A 1
>>> A 2
>>> A 3
>>> A 4
>>> B 1
>>> B 3
>>> C 2
>>> C 4
>>> I want my output to be:
>>> COL1 COL2
>>> ---- ----
>>> B 2
>>> B 4
>>> C 1
>>> C 3
>>>
>>> I thought I could do this with some outer joins but I can't seem to
>>> get even close. Any suggestions? Thanks!
>> what are all the potential values that col2 can have?
>>
>> --
>> lark -- ham...@sbcdeglobalspam.net
>> To reply to me directly, delete "despam".
>
> Whatever the unique values that exist in column 2 are.
>
if there were null value for the missing pairs in the col2, this would
have been a bit simpler however, as it is this is hard to implement in
sql. because you'd have to have a loop to find out what all the values
are you're trying to compare to.
i think you'd be better off with a stored procedure because in an sp you
can have loops and other controls just like a programming language.
you'll write an sp that starts parsing the table by doing a select
distinct on col2. it then starts comparing the value of each row to the
ones it's found. be careful to include a group by so that you'd know
col2 values all belong to the same col1 value.
hope this can help you!
--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".
On May 4, 3:02 pm, lark
> jimn...@hotmail.com wrote:
> > On May 4, 9:43 am, lark
> >> jimn...@hotmail.com wrote:
> >>> I'm trying to create a query to find missing values in a table.
> >>> Example table:
> >>>
> >>> COL1 COL2
> >>> ---- ----
> >>> A 1
> >>> A 2
> >>> A 3
> >>> A 4
> >>> B 1
> >>> B 3
> >>> C 2
> >>> C 4
> >>> I want my output to be:
> >>> COL1 COL2
> >>> ---- ----
> >>> B 2
> >>> B 4
> >>> C 1
> >>> C 3
> >>>
> >>> I thought I could do this with some outer joins but I can't seem to
> >>> get even close. Any suggestions? Thanks!
> >> what are all the potential values that col2 can have?
>
> >> --
> >> lark -- ham...@sbcdeglobalspam.net
> >> To reply to me directly, delete "despam".
>
> > Whatever the unique values that exist in column 2 are.
>
> if there were null value for the missing pairs in the col2, this would
> have been a bit simpler however, as it is this is hard to implement in
> sql. because you'd have to have a loop to find out what all the values
> are you're trying to compare to.
>
> i think you'd be better off with a stored procedure because in an sp you
> can have loops and other controls just like a programming language.
>
> you'll write an sp that starts parsing the table by doing a select
> distinct on col2. it then starts comparing the value of each row to the
> ones it's found. be careful to include a group by so that you'd know
> col2 values all belong to the same col1 value.
>
> hope this can help you!
>
> --
> lark -- ham...@sbcdeglobalspam.net
> To reply to me directly, delete "despam".
That's what I figured, that I was asking for too much out of a single
SQL query. Thanks!
jimnl69@hotmail.com wrote:
> On May 4, 3:02 pm, lark
> > jimn...@hotmail.com wrote:
> > > On May 4, 9:43 am, lark
> > >> jimn...@hotmail.com wrote:
> > >>> I'm trying to create a query to find missing values in a table.
> > >>> Example table:
> > >>>
> > >>> COL1 COL2
> > >>> ---- ----
> > >>> A 1
> > >>> A 2
> > >>> A 3
> > >>> A 4
> > >>> B 1
> > >>> B 3
> > >>> C 2
> > >>> C 4
> > >>> I want my output to be:
> > >>> COL1 COL2
> > >>> ---- ----
> > >>> B 2
> > >>> B 4
> > >>> C 1
> > >>> C 3
> > >>>
untested:
SELECT t2.col1,t1.col2 FROM my_table t1
LEFT JOIN my_table t2 ON t2.row_id = t1.row_id
WHERE t2.col1 IS NULL
untested:
SELECT DISTINCT(t2.col1),t1.col2 FROM my_table t1
LEFT JOIN my_table t2 ON t2.row_id = t1.row_id
WHERE t2.col1 IS NULL
On May 7, 7:22 am, strawberry
> untested:
>
> SELECT DISTINCT(t2.col1),t1.col2 FROM my_table t1
> LEFT JOIN my_table t2 ON t2.row_id = t1.row_id
> WHERE t2.col1 IS NULL
Thanks, but what is row_id? The only columns I have are the ones I
listed.
On May 7, 1:52 pm, jimn...@hotmail.com wrote:
> On May 7, 7:22 am, strawberry
>
> > untested:
>
> > SELECT DISTINCT(t2.col1),t1.col2 FROM my_table t1
> > LEFT JOIN my_table t2 ON t2.row_id = t1.row_id
> > WHERE t2.col1 IS NULL
>
> Thanks, but what is row_id? The only columns I have are the ones I
> listed.
You first need to derive a table of every POSSIBLE combination (b),
and then compare that table with every VALID combination (a),
something like this. Because you don't have a single-cell PRIMARY KEY
I made one (row_id) using the CONCAT function. I'm not sure that it's
necessary but it helped me to think about the problem:
SELECT b.col1, b.col2
FROM my_table a
RIGHT JOIN (
SELECT DISTINCT (
CONCAT( t1.col1, t2.col2 )
)row_id, t1.col1, t2.col2
FROM `my_table` t1, my_table t2
ORDER BY t1.col1, t2.col2
)b ON a.col1 = b.col1
AND a.col2 = b.col2
WHERE ISNULL( a.col1 )
LIMIT 0 , 30
On May 7, 10:12 am, strawberry
> On May 7, 1:52 pm, jimn...@hotmail.com wrote:> On May 7, 7:22 am, strawberry
>
> > > untested:
>
> > > SELECT DISTINCT(t2.col1),t1.col2 FROM my_table t1
> > > LEFT JOIN my_table t2 ON t2.row_id = t1.row_id
> > > WHERE t2.col1 IS NULL
>
> > Thanks, but what is row_id? The only columns I have are the ones I
> > listed.
>
> You first need to derive a table of every POSSIBLE combination (b),
> and then compare that table with every VALID combination (a),
> something like this. Because you don't have a single-cell PRIMARY KEY
> I made one (row_id) using the CONCAT function. I'm not sure that it's
> necessary but it helped me to think about the problem:
>
> SELECT b.col1, b.col2
> FROM my_table a
> RIGHT JOIN (
>
> SELECT DISTINCT (
> CONCAT( t1.col1, t2.col2 )
> )row_id, t1.col1, t2.col2
> FROM `my_table` t1, my_table t2
> ORDER BY t1.col1, t2.col2
> )b ON a.col1 = b.col1
> AND a.col2 = b.col2
> WHERE ISNULL( a.col1 )
> LIMIT 0 , 30
Fantastic! Now, I need to spend some time studying it to see exactly
what's going on. Thanks so much!