Dreamweaver - SQL query for orphaned files (PART II)

Author

SQL query for orphaned files (PART II)

darrel

2005-03-30, 11:15 pm

I have a set of tables that are all related that I'm running a complex query
on. I'm missing 9 records from one of the tables that should be showing up.
I'm trying to figure out which 9 records these are.

What I've done is taking my query and inserted that into a new table. So I
now have two tables where one has 400 records, and the other 391. I'm trying
to figure out what kind of query I can do to find all records that show up
in the first table, but not the second.

And I'm stumped. I'm guessing this might be blatantly easy once I have the
answer. ;o)

-Darrel

Baxter

2005-03-30, 11:21 pm

Hi! Darrel
This should be what you want.
SELECT [Table1].[PartID], [Table1].[PartName], [Table1].[UnitPrice]
FROM Table1 LEFT JOIN Table2 ON [Table1].[PartID] = [Table2].[PartID]
WHERE ([Table2].[PartID] Is Null);
the results will be the nine missing records.
Change table names and field names to match yours.
Dave

"darrel" <notreal@hotmail.com> wrote in message
news:d2fcni$ebq$1@forums.macromedia.com...
> I have a set of tables that are all related that I'm running a complex
query
> on. I'm missing 9 records from one of the tables that should be showing
up.
> I'm trying to figure out which 9 records these are.
>
> What I've done is taking my query and inserted that into a new table. So I
> now have two tables where one has 400 records, and the other 391. I'm
trying
> to figure out what kind of query I can do to find all records that show up
> in the first table, but not the second.
>
> And I'm stumped. I'm guessing this might be blatantly easy once I have the
> answer. ;o)
>
> -Darrel
>
>

Jeff North

2005-03-30, 11:21 pm

On Wed, 30 Mar 2005 17:29:58 -0600, in macromedia.dreamweaver "darrel"
<notreal@hotmail.com> wrote:

>| I have a set of tables that are all related that I'm running a complex query
>| on. I'm missing 9 records from one of the tables that should be showing up.
>| I'm trying to figure out which 9 records these are.
>|
>| What I've done is taking my query and inserted that into a new table. So I
>| now have two tables where one has 400 records, and the other 391. I'm trying
>| to figure out what kind of query I can do to find all records that show up
>| in the first table, but not the second.
>|
>| And I'm stumped. I'm guessing this might be blatantly easy once I have the
>| answer. ;o)

SELECT table1.field1,table1.field2,table1.field3
FROM table1 LEFT JOIN table2 ON table1.fkID = table2.pkID
WHERE table2.pkID Is Null;

---------------------------------------------------------------
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

Paul Woodward

2005-03-31, 6:26 pm

"Jeff North" <jnorthau@yahoo.com.au> wrote in message
news:v1om41t2m0m3b318sr8aeh3aqj0hn9nvq5@4ax.com...
On Wed, 30 Mar 2005 17:29:58 -0600, in macromedia.dreamweaver "darrel"
<notreal@hotmail.com> wrote:

>| I have a set of tables that are all related that I'm running a complex
>query
>| on. I'm missing 9 records from one of the tables that should be showing
>up.
>| I'm trying to figure out which 9 records these are.
>|
>| What I've done is taking my query and inserted that into a new table. So
>I
>| now have two tables where one has 400 records, and the other 391. I'm
>trying
>| to figure out what kind of query I can do to find all records that show
>up
>| in the first table, but not the second.
>|
>| And I'm stumped. I'm guessing this might be blatantly easy once I have
>the
>| answer. ;o)

SELECT table1.field1,table1.field2,table1.field3
FROM table1 LEFT JOIN table2 ON table1.fkID = table2.pkID
WHERE table2.pkID Is Null;

---------------------------------------------------------------
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

===

Hi Jeff,

I am not sure what database engine you're using so I will assume MySQL,
either way the SQL syntax is generally very similar for all SQL Database
Engines.

Click the link below and that should provide you with some useful
information regarding your problem: -

http://dev.mysql.com/doc/mysql/en/join.html

Look a few lines from the top for the following information which I think is
exactly what your looking for: -

"If there is no matching record for the right table in the ON or USING part
in a LEFT JOIN, a row with all columns set to NULL is used for the right
table. You can use this fact to find records in a table that have no
counterpart in another table"

Regards,

Paul Woodward

darrel

2005-03-31, 6:31 pm

> Hi! Darrel
> This should be what you want.
> SELECT [Table1].[PartID], [Table1].[PartName], [Table1].[UnitPrice]
> FROM Table1 LEFT JOIN Table2 ON [Table1].[PartID] = [Table2].[PartID]
> WHERE ([Table2].[PartID] Is Null);
> the results will be the nine missing records.
> Change table names and field names to match yours.
> Dave

Thanks Dave. Alas, that doesn't work. It doesn't return any records.

-Darrel

Baxter

2005-03-31, 6:44 pm

Darrel
Table1 should be the table with 400 records Table2 is the table with 391
records I know it will work as I ran the same query on a database I have.
If you want you can email me the database and I'll do it for you. baxter
at gtlakes dot com.
Dave
"darrel" <notreal@hotmail.com> wrote in message
news:d2h4jm$208$1@forums.macromedia.com...
>
>
> Thanks Dave. Alas, that doesn't work. It doesn't return any records.
>
> -Darrel
>
>