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.

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_fiels WHERE
ISNULL(t2.primary_key_field)

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)

Apologies if I pressed send twice!

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_fiels WHERE
> ISNULL(t2.primary_key_field)

Except that there is no point having t2.primary_key_field in the select
list if the WHERE clause is ISNULL(t2.primary_key_field) as it will
always be NULL!

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

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
)

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
> )

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