comparing two tables

Cleverbum wrote:
> Forgive me if you think the subject is wrong, but I can't quite think
> how to describe my question.
> I just deleted some rows of a table and need to see if there were any
> values in another table linked to the rows I deleted.
> I'm looking for a query that would perform:
> Show all records from table1 where the value in column `album` is not
> contained in the any of the rows in column `uid` of table2
> If that makes no sense let me know and I'll try to re-word it and write
> an example.

select album from table_1
union
select uid from table_2
where
uid not in
(select album from table_1)

I think that seems to be what you require.

If not, sorry! I mis-understood, or I'm just plain
stupid!

Mark Addinall.

addinall wrote:
> Cleverbum wrote:
> > Forgive me if you think the subject is wrong, but I can't quite think
> > how to describe my question.
> > I just deleted some rows of a table and need to see if there were any
> > values in another table linked to the rows I deleted.
> > I'm looking for a query that would perform:
> > Show all records from table1 where the value in column `album` is not
> > contained in the any of the rows in column `uid` of table2
> > If that makes no sense let me know and I'll try to re-word it and write
> > an example.
>
> select album from table_1
> union
> select uid from table_2
> where
> uid not in
> (select album from table_1)
>
> I think that seems to be what you require.
>
> If not, sorry! I mis-understood, or I'm just plain
> stupid!
>
> Mark Addinall.

That seems closer, what actually seems to do the trick is:

SELECT *
FROM personal_images
WHERE album NOT
IN (

SELECT uid
FROM personal_albums
)

addinall wrote:
> Cleverbum wrote:
> > Forgive me if you think the subject is wrong, but I can't quite think
> > how to describe my question.
> > I just deleted some rows of a table and need to see if there were any
> > values in another table linked to the rows I deleted.
> > I'm looking for a query that would perform:
> > Show all records from table1 where the value in column `album` is not
> > contained in the any of the rows in column `uid` of table2
> > If that makes no sense let me know and I'll try to re-word it and write
> > an example.
>
> select album from table_1
> union
> select uid from table_2
> where
> uid not in
> (select album from table_1)
>
> I think that seems to be what you require.
>
> If not, sorry! I mis-understood, or I'm just plain
> stupid!
>
> Mark Addinall.

That seems closer, what actually seems to do the trick is:

SELECT *
FROM personal_images
WHERE album NOT
IN (

SELECT uid
FROM personal_albums
)

Cleverbum wrote:

> strawberry wrote:
> > Cleverbum wrote:
> >
> > > Forgive me if you think the subject is wrong, but I can't quite think
> > > how to describe my question.
> > > I just deleted some rows of a table and need to see if there were any
> > > values in another table linked to the rows I deleted.
> > > I'm looking for a query that would perform:
> > > Show all records from table1 where the value in column `album` is not
> > > contained in the any of the rows in column `uid` of table2
> > > If that makes no sense let me know and I'll try to re-word it and write
> > > an example.
> >
> > You'll probably want a query along the these lines (untested):
> >
> > SELECT t1.primary_key_field,t2.primary_key_field FROM t1 LEFT JOIN t2
> > ON t2.foreign_key_field = t1.primary_key_field WHERE
> > ISNULL(t2.primary_key_field)
> >
>
> This appears to return all permutations of the two tables, which is one
> heck of a lot!
>
> an example:
> Table one
> col1 -- col2 -- col3
> 1 -- one -- blah
> 2 -- two -- blah
> 3 -- three -- blah
>
> Table two
> col1 -- col2
> two -- 2
> one -- 1
>
> What I would like to do is execute a query telling me that there is no
> row in table two column one containing 'three' even though three is
> contained in one of the rows of table one column 2

Well the following query does that:

SELECT DISTINCT t1.col1
FROM `table_one` t1
LEFT JOIN `table_two` t2 ON t1.col2 = t2.col1
WHERE t2.col1 IS NULL

Which is pretty much what Strawberry suggested.

Cleverbum wrote:

> strawberry wrote:
> > Cleverbum wrote:
> >
> > > Forgive me if you think the subject is wrong, but I can't quite think
> > > how to describe my question.
> > > I just deleted some rows of a table and need to see if there were any
> > > values in another table linked to the rows I deleted.
> > > I'm looking for a query that would perform:
> > > Show all records from table1 where the value in column `album` is not
> > > contained in the any of the rows in column `uid` of table2
> > > If that makes no sense let me know and I'll try to re-word it and write
> > > an example.
> >
> > You'll probably want a query along the these lines (untested):
> >
> > SELECT t1.primary_key_field,t2.primary_key_field FROM t1 LEFT JOIN t2
> > ON t2.foreign_key_field = t1.primary_key_field WHERE
> > ISNULL(t2.primary_key_field)
> >
>
> This appears to return all permutations of the two tables, which is one
> heck of a lot!
>
> an example:
> Table one
> col1 -- col2 -- col3
> 1 -- one -- blah
> 2 -- two -- blah
> 3 -- three -- blah
>
> Table two
> col1 -- col2
> two -- 2
> one -- 1
>
> What I would like to do is execute a query telling me that there is no
> row in table two column one containing 'three' even though three is
> contained in one of the rows of table one column 2

Well the following query does that:

SELECT DISTINCT t1.col1
FROM `table_one` t1
LEFT JOIN `table_two` t2 ON t1.col2 = t2.col1
WHERE t2.col1 IS NULL

Which is pretty much what Strawberry suggested.

Cleverbum wrote:
> addinall wrote:
> > Cleverbum wrote:
> > > Forgive me if you think the subject is wrong, but I can't quite think
> > > how to describe my question.
> > > I just deleted some rows of a table and need to see if there were any
> > > values in another table linked to the rows I deleted.
> > > I'm looking for a query that would perform:
> > > Show all records from table1 where the value in column `album` is not
> > > contained in the any of the rows in column `uid` of table2
> > > If that makes no sense let me know and I'll try to re-word it and write
> > > an example.
> >
> > select album from table_1
> > union
> > select uid from table_2
> > where
> > uid not in
> > (select album from table_1)
> >
> > I think that seems to be what you require.
> >
> > If not, sorry! I mis-understood, or I'm just plain
> > stupid!
> >
> > Mark Addinall.
>
> That seems closer, what actually seems to do the trick is:
>
> SELECT *
> FROM personal_images
> WHERE album NOT
> IN (
>
> SELECT uid
> FROM personal_albums
> )

Yeah. Implied unions are fine. I'm an ORACLE head,
but I always take SQL back to relational algreba or
relational calculus if I have a squirmy query.

Not all SQL engines give us the choice of
implied unions or joins!

In that case, depending on your indices;

select %ROWNUM, album from personal_images
where
album not in
(select uid from personal_albums)

Depending on the size and structure of your
database and schema, you could chuck in
some %HINTS%. Cuts down on your temp
tables, and the time to store them.


I'm glad you got it.

Mark Addinall.

Cleverbum wrote:
> addinall wrote:
> > Cleverbum wrote:
> > > Forgive me if you think the subject is wrong, but I can't quite think
> > > how to describe my question.
> > > I just deleted some rows of a table and need to see if there were any
> > > values in another table linked to the rows I deleted.
> > > I'm looking for a query that would perform:
> > > Show all records from table1 where the value in column `album` is not
> > > contained in the any of the rows in column `uid` of table2
> > > If that makes no sense let me know and I'll try to re-word it and write
> > > an example.
> >
> > select album from table_1
> > union
> > select uid from table_2
> > where
> > uid not in
> > (select album from table_1)
> >
> > I think that seems to be what you require.
> >
> > If not, sorry! I mis-understood, or I'm just plain
> > stupid!
> >
> > Mark Addinall.
>
> That seems closer, what actually seems to do the trick is:
>
> SELECT *
> FROM personal_images
> WHERE album NOT
> IN (
>
> SELECT uid
> FROM personal_albums
> )

Yeah. Implied unions are fine. I'm an ORACLE head,
but I always take SQL back to relational algreba or
relational calculus if I have a squirmy query.

Not all SQL engines give us the choice of
implied unions or joins!

In that case, depending on your indices;

select %ROWNUM, album from personal_images
where
album not in
(select uid from personal_albums)

Depending on the size and structure of your
database and schema, you could chuck in
some %HINTS%. Cuts down on your temp
tables, and the time to store them.


I'm glad you got it.

Mark Addinall.

Cleverbum wrote:

> addinall wrote:
> > Cleverbum wrote:
> > > Forgive me if you think the subject is wrong, but I can't quite think
> > > how to describe my question.
> > > I just deleted some rows of a table and need to see if there were any
> > > values in another table linked to the rows I deleted.
> > > I'm looking for a query that would perform:
> > > Show all records from table1 where the value in column `album` is not
> > > contained in the any of the rows in column `uid` of table2
> > > If that makes no sense let me know and I'll try to re-word it and write
> > > an example.
> >
> > select album from table_1
> > union
> > select uid from table_2
> > where
> > uid not in
> > (select album from table_1)
> >
> > I think that seems to be what you require.
> >
> > If not, sorry! I mis-understood, or I'm just plain
> > stupid!
> >
> > Mark Addinall.
>
> That seems closer, what actually seems to do the trick is:
>
> SELECT *
> FROM personal_images
> WHERE album NOT
> IN (
>
> SELECT uid
> FROM personal_albums
> )

This query will produce exactly the asme results as Strawberry's LEFT
JOIN compare with NULL query.

The difference is that the LEFT join is much much much more efficient,
especially if there is an appropriate index.

See http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

Cleverbum wrote:

> addinall wrote:
> > Cleverbum wrote:
> > > Forgive me if you think the subject is wrong, but I can't quite think
> > > how to describe my question.
> > > I just deleted some rows of a table and need to see if there were any
> > > values in another table linked to the rows I deleted.
> > > I'm looking for a query that would perform:
> > > Show all records from table1 where the value in column `album` is not
> > > contained in the any of the rows in column `uid` of table2
> > > If that makes no sense let me know and I'll try to re-word it and write
> > > an example.
> >
> > select album from table_1
> > union
> > select uid from table_2
> > where
> > uid not in
> > (select album from table_1)
> >
> > I think that seems to be what you require.
> >
> > If not, sorry! I mis-understood, or I'm just plain
> > stupid!
> >
> > Mark Addinall.
>
> That seems closer, what actually seems to do the trick is:
>
> SELECT *
> FROM personal_images
> WHERE album NOT
> IN (
>
> SELECT uid
> FROM personal_albums
> )

This query will produce exactly the asme results as Strawberry's LEFT
JOIN compare with NULL query.

The difference is that the LEFT join is much much much more efficient,
especially if there is an appropriate index.

See http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

Captain Paralytic wrote:
> Cleverbum wrote:
>
> > strawberry wrote:
> > > Cleverbum wrote:
> > >
> > > > Forgive me if you think the subject is wrong, but I can't quite think
> > > > how to describe my question.
> > > > I just deleted some rows of a table and need to see if there were any
> > > > values in another table linked to the rows I deleted.
> > > > I'm looking for a query that would perform:
> > > > Show all records from table1 where the value in column `album` is not
> > > > contained in the any of the rows in column `uid` of table2
> > > > If that makes no sense let me know and I'll try to re-word it and write
> > > > an example.
> > >
> > > You'll probably want a query along the these lines (untested):
> > >
> > > SELECT t1.primary_key_field,t2.primary_key_field FROM t1 LEFT JOIN t2
> > > ON t2.foreign_key_field = t1.primary_key_field WHERE
> > > ISNULL(t2.primary_key_field)
> > >
> >
> > This appears to return all permutations of the two tables, which is one
> > heck of a lot!
> >
> > an example:
> > Table one
> > col1 -- col2 -- col3
> > 1 -- one -- blah
> > 2 -- two -- blah
> > 3 -- three -- blah
> >
> > Table two
> > col1 -- col2
> > two -- 2
> > one -- 1
> >
> > What I would like to do is execute a query telling me that there is no
> > row in table two column one containing 'three' even though three is
> > contained in one of the rows of table one column 2
>
> Well the following query does that:
>
> SELECT DISTINCT t1.col1
> FROM `table_one` t1
> LEFT JOIN `table_two` t2 ON t1.col2 = t2.col1
> WHERE t2.col1 IS NULL
>
> Which is pretty much what Strawberry suggested.

Not a slur, an observation. Coming out of big Iron
into the world of MySQL I see a lot of constructs
like this, when standard, and simpler SQL can
achieve the same result.

The above probably does work, but it's not very
obvious what it is doing, unless preceded by
structured English documentation.

I have come across code in the last few years
that have LEFT, INNER and OUTER joins,
all mixed up in multiple SELECT statements,
which made no sense at all. Truly, a 100 line
SQL statement is a little horrible.

Mark Addinall.