JOINS and/or INTERSECTS

[jimnl69@hotmail.com]

I'm trying to create a query to find missing values in a table.
Example table:
<PRE>
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
</PRE>
I thought I could do this with some outer joins but I can't seem to
get even close. Any suggestions? Thanks!

[lark]

Re: JOINS and/or INTERSECTS

jimnl69@hotmail .com wrote:

I'm trying to create a query to find missing values in a table.
Example table:
<PRE>
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
</PRE>
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 -- hamzee@sbcdeglo balspam.net
To reply to me directly, delete "despam".

[jimnl69@hotmail.com]

Re: JOINS and/or INTERSECTS

On May 4, 9:43 am, lark <ham...@sbcdegl obalspam.netwro te:

jimn...@hotmail .com wrote:

I'm trying to create a query to find missing values in a table.
Example table:
<PRE>
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
</PRE>
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...@sbcdeglo balspam.net
To reply to me directly, delete "despam".

Whatever the unique values that exist in column 2 are.

[lark]

Re: JOINS and/or INTERSECTS

jimnl69@hotmail .com wrote:

On May 4, 9:43 am, lark <ham...@sbcdegl obalspam.netwro te:

>jimn...@hotmai l.com wrote:

>>I'm trying to create a query to find missing values in a table.
>>Example table:
>><PRE>
>>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
>></PRE>
>>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...@sbcdeglo balspam.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@sbcdeglo balspam.net
To reply to me directly, delete "despam".

[jimnl69@hotmail.com]

Re: JOINS and/or INTERSECTS

On May 4, 3:02 pm, lark <ham...@sbcdegl obalspam.netwro te:

jimn...@hotmail .com wrote:

On May 4, 9:43 am, lark <ham...@sbcdegl obalspam.netwro te:

jimn...@hotmail .com wrote:
>I'm trying to create a query to find missing values in a table.
>Example table:
><PRE>
>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
></PRE>
>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...@sbcdeglo balspam.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...@sbcdeglo balspam.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!

[strawberry]

Re: JOINS and/or INTERSECTS


jimnl69@hotmail .com wrote:

On May 4, 3:02 pm, lark <ham...@sbcdegl obalspam.netwro te:

jimn...@hotmail .com wrote:

On May 4, 9:43 am, lark <ham...@sbcdegl obalspam.netwro te:
>jimn...@hotmai l.com wrote:
>>I'm trying to create a query to find missing values in a table.
>>Example table:
>><PRE>
>>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
>></PRE>

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

[strawberry]

Re: JOINS and/or INTERSECTS

untested:


SELECT DISTINCT(t2.col 1),t1.col2 FROM my_table t1
LEFT JOIN my_table t2 ON t2.row_id = t1.row_id
WHERE t2.col1 IS NULL

[jimnl69@hotmail.com]

Re: JOINS and/or INTERSECTS

On May 7, 7:22 am, strawberry <zac.ca...@gmai l.comwrote:

untested:
>
SELECT DISTINCT(t2.col 1),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.

[strawberry]

Re: JOINS and/or INTERSECTS

On May 7, 1:52 pm, jimn...@hotmail .com wrote:

On May 7, 7:22 am, strawberry <zac.ca...@gmai l.comwrote:
>

untested:

>

SELECT DISTINCT(t2.col 1),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

[jimnl69@hotmail.com]

Re: JOINS and/or INTERSECTS

On May 7, 10:12 am, strawberry <zac.ca...@gmai l.comwrote:

On May 7, 1:52 pm, jimn...@hotmail .com wrote:On May 7, 7:22 am, strawberry <zac.ca...@gmai l.comwrote:
>

untested:

>

SELECT DISTINCT(t2.col 1),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!