JOINS and/or INTERSECTS

Post by j***@hotmail.com
I'm trying to create a query to find missing values in a table.
<PRE>
COL1 COL2
---- ----
A 1
A 2
A 3
A 4
B 1
B 3
C 2
C 4
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 -- ***@sbcdeglobalspam.net
To reply to me directly, delete "despam".

j***@hotmail.com

2007-05-04 18:30:38 UTC

what are all the potential values that col2 can have?
--
To reply to me directly, delete "despam".

Whatever the unique values that exist in column 2 are.

lark

2007-05-04 19:02:38 UTC

what are all the potential values that col2 can have?
--
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 -- ***@sbcdeglobalspam.net
To reply to me directly, delete "despam".

j***@hotmail.com

2007-05-04 19:23:28 UTC

what are all the potential values that col2 can have?
--
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!
--
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

2007-05-07 11:17:43 UTC

Post by j***@hotmail.com
I'm trying to create a query to find missing values in a table.
<PRE>
COL1 COL2
---- ----
A 1
A 2
A 3
A 4
B 1
B 3
C 2
C 4
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

2007-05-07 11:22:42 UTC

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

j***@hotmail.com

2007-05-07 12:52:02 UTC

Post by strawberry
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.

strawberry

2007-05-07 14:12:17 UTC

Post by strawberry
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

j***@hotmail.com

2007-05-07 15:53:34 UTC

Post by strawberry