SQL help

[ryan.fitzpatrick3]

I have this SQL code below. It takes 5 tables which for example are
1,2,3,4,5, 5 is subtable of 4, 4 of 3, 3 of 2, and 2 of 1. So when you
open table 1 up it can go to table 5. This code below takes those 5
tables and only finds the unique value in compared table. So lets say:

table 1

auto# Class
1 Ingredients
2 Packaging

Currently the subtable (table 2) has both ingredients and packaging
items together, it would look like this

autonum Category Class
1 Commodities Ingredients
2 Crops Ingredients
3 Dairy Ingredients
4 Label Packaging
5 Resin Packaging
6 Corrugated Packaging
....
17

table 3 or subsubtable would look like, this table has all category
items together, ultimately both ingredient and packaging.

GroupItems Category
1 Oil Commodities
2 Flour Commodities
3 Fruit Crops
4 Nuts Crops
5 Milk Dairy
6 Resin LDPE Resin
7 Promo Label
....
40


The code I should work like this. In table 3 it has Oil which is a
Commodity, so the code should eliminate Commodity out of the returned
query results, and so on for the rest of the items. Here is the code.

SELECT [tblclass].classNAME
FROM [tblClass]
WHERE ClassName NOT IN (SELECT DISTINCT categoryname FROM
tblCategories)
UNION
SELECT [tblCategories].categoryname
FROM [tblCategories]
WHERE categoryname NOT IN (SELECT DISTINCT groupName FROM tblgroup)
UNION
SELECT [tblgroup].groupNAME
FROM [tblgroup]
WHERE groupNAME NOT IN (SELECT DISTINCT ItemClassName FROM
tblItemClass)
UNION
SELECT [tblItemClass].itemclassname
FROM [tblItemClass]
WHERE ItemClassName NOT IN (SELECT DISTINCT ItemSubClassName FROM
tblItemSubClass)
UNION SELECT [tblItemsubClass].itemsubclassNAME
FROM [tblItemsubClass];


Basically it'll take the last most unique record in each table
providing that there is not a subtable that has information for that
one record. Any ideas. currently it just pulls everything all items
from table 1 to 5.

Ryan

 

[George Nicholson]

currently it just pulls everything all items from table 1 to 5.

Your subqueries are all looking in the wrong fields. Since no matches are
found, NOT IN() returns all records from each table.
If your data is set up right, you won't find Apples among the Oranges

The following (with "Correct" field names substituted) should give you a
complete list of itemsubclassNAMEs in table 5 and any non-matching names
from higher up the data structure.

I have added a field to indicate the source of the name, but that can be
omitted if you want. I include something similar in most of my UNIONS, since
it makes them easier to debug/test.

SELECT "Class Name" as TableSource, classNAME As NameEntry FROM [tblClass]
WHERE ClassName NOT IN (SELECT DISTINCT CorrectClassNameField FROM
tblCategories)

UNION SELECT "Category Name", categoryname FROM [tblCategories]
WHERE categoryname NOT IN (SELECT DISTINCT CorrectCategoryNameField FROM
tblgroup)

UNION SELECT "Group Name", groupNAME FROM [tblgroup]
WHERE groupNAME NOT IN (SELECT DISTINCT CorrectGroupNameField FROM
tblItemClass)

UNION SELECT "Item Class Name", itemclassname FROM [tblItemClass]
WHERE ItemClassName NOT IN (SELECT DISTINCT CorrectItemClassName FROM
tblItemSubClass)

UNION SELECT "Item Subclass Name", itemsubclassNAME FROM [tblItemsubClass];


--
HTH,
George

I have this SQL code below. It takes 5 tables which for example are
1,2,3,4,5, 5 is subtable of 4, 4 of 3, 3 of 2, and 2 of 1. So when you
open table 1 up it can go to table 5. This code below takes those 5
tables and only finds the unique value in compared table. So lets say:

table 1

auto# Class
1 Ingredients
2 Packaging

Currently the subtable (table 2) has both ingredients and packaging
items together, it would look like this

autonum Category Class
1 Commodities Ingredients
2 Crops Ingredients
3 Dairy Ingredients
4 Label Packaging
5 Resin Packaging
6 Corrugated Packaging
...
17

table 3 or subsubtable would look like, this table has all category
items together, ultimately both ingredient and packaging.

GroupItems Category
1 Oil Commodities
2 Flour Commodities
3 Fruit Crops
4 Nuts Crops
5 Milk Dairy
6 Resin LDPE Resin
7 Promo Label
...
40


The code I should work like this. In table 3 it has Oil which is a
Commodity, so the code should eliminate Commodity out of the returned
query results, and so on for the rest of the items. Here is the code.

SELECT [tblclass].classNAME
FROM [tblClass]
WHERE ClassName NOT IN (SELECT DISTINCT categoryname FROM
tblCategories)
UNION
SELECT [tblCategories].categoryname
FROM [tblCategories]
WHERE categoryname NOT IN (SELECT DISTINCT groupName FROM tblgroup)
UNION
SELECT [tblgroup].groupNAME
FROM [tblgroup]
WHERE groupNAME NOT IN (SELECT DISTINCT ItemClassName FROM
tblItemClass)
UNION
SELECT [tblItemClass].itemclassname
FROM [tblItemClass]
WHERE ItemClassName NOT IN (SELECT DISTINCT ItemSubClassName FROM
tblItemSubClass)
UNION SELECT [tblItemsubClass].itemsubclassNAME
FROM [tblItemsubClass];


Basically it'll take the last most unique record in each table
providing that there is not a subtable that has information for that
one record. Any ideas. currently it just pulls everything all items
from table 1 to 5.

Ryan

 

[ryan.fitzpatrick3]

Thank you for the reply. When I ran the query an input box popped up
looking for these

CorrectClassNameField
CorrectCategoryNameField
CorrectGroupNameField
etc

when I ok'd them to bypass it showed everything in table 5 like you
mentioned would show. But, how do I eliminate the input boxes and if
table 4 is the end of the line for one item and it can't be sub-
categorized into a lower table, table 5 for example, how do I get it
do get the last of table 4 or table 3 if that was the end of the road
for that item?

Ryan

currently it just pulls everything all items from table 1 to 5.

Your subqueries are all looking in the wrong fields. Since no matches are
found, NOT IN() returns all records from each table.
If your data is set up right, you won't find Apples among the Oranges

The following (with "Correct" field names substituted) should give you a
complete list of itemsubclassNAMEs in table 5 and any non-matching names
from higher up the data structure.

I have added a field to indicate the source of the name, but that can be
omitted if you want. I include something similar in most of my UNIONS, since
it makes them easier to debug/test.

SELECT "Class Name" as TableSource, classNAME As NameEntry FROM [tblClass]
WHERE ClassName NOT IN (SELECT DISTINCT CorrectClassNameField FROM
tblCategories)

UNION SELECT "Category Name", categoryname FROM [tblCategories]
WHERE categoryname NOT IN (SELECT DISTINCT CorrectCategoryNameField FROM
tblgroup)

UNION SELECT "Group Name", groupNAME FROM [tblgroup]
WHERE groupNAME NOT IN (SELECT DISTINCT CorrectGroupNameField FROM
tblItemClass)

UNION SELECT "Item Class Name", itemclassname FROM [tblItemClass]
WHERE ItemClassName NOT IN (SELECT DISTINCT CorrectItemClassName FROM
tblItemSubClass)

UNION SELECT "Item Subclass Name", itemsubclassNAME FROM [tblItemsubClass];

--
HTH,
George

I have this SQL code below. It takes 5 tables which for example are
1,2,3,4,5, 5 is subtable of 4, 4 of 3, 3 of 2, and 2 of 1. So when you
open table 1 up it can go to table 5. This code below takes those 5
tables and only finds the unique value in compared table. So lets say:

table 1

auto# Class
1 Ingredients
2 Packaging

Currently the subtable (table 2) has both ingredients and packaging
items together, it would look like this

autonum Category Class
1 Commodities Ingredients
2 Crops Ingredients
3 Dairy Ingredients
4 Label Packaging
5 Resin Packaging
6 Corrugated Packaging
...
17

table 3 or subsubtable would look like, this table has all category
items together, ultimately both ingredient and packaging.

GroupItems Category
1 Oil Commodities
2 Flour Commodities
3 Fruit Crops
4 Nuts Crops
5 Milk Dairy
6 Resin LDPE Resin
7 Promo Label
...
40

The code I should work like this. In table 3 it has Oil which is a
Commodity, so the code should eliminate Commodity out of the returned
query results, and so on for the rest of the items. Here is the code.

SELECT [tblclass].classNAME
FROM [tblClass]
WHERE ClassName NOT IN (SELECT DISTINCT categoryname FROM
tblCategories)
UNION
SELECT [tblCategories].categoryname
FROM [tblCategories]
WHERE categoryname NOT IN (SELECT DISTINCT groupName FROM tblgroup)
UNION
SELECT [tblgroup].groupNAME
FROM [tblgroup]
WHERE groupNAME NOT IN (SELECT DISTINCT ItemClassName FROM
tblItemClass)
UNION
SELECT [tblItemClass].itemclassname
FROM [tblItemClass]
WHERE ItemClassName NOT IN (SELECT DISTINCT ItemSubClassName FROM
tblItemSubClass)
UNION SELECT [tblItemsubClass].itemsubclassNAME
FROM [tblItemsubClass];

Basically it'll take the last most unique record in each table
providing that there is not a subtable that has information for that
one record. Any ideas. currently it just pulls everything all items
from table 1 to 5.

Ryan

 

[George Nicholson]

(with "Correct" field names substituted)

was my way of saying: replace the "correct xxx" field names in my code with
the actual field names in your tables.

I believe that correcting the field names should resolve your other question
as well.

--
HTH,
George

Thank you for the reply. When I ran the query an input box popped up
looking for these

CorrectClassNameField
CorrectCategoryNameField
CorrectGroupNameField
etc

when I ok'd them to bypass it showed everything in table 5 like you
mentioned would show. But, how do I eliminate the input boxes and if
table 4 is the end of the line for one item and it can't be sub-
categorized into a lower table, table 5 for example, how do I get it
do get the last of table 4 or table 3 if that was the end of the road
for that item?

Ryan

currently it just pulls everything all items from table 1 to 5.

Your subqueries are all looking in the wrong fields. Since no matches are
found, NOT IN() returns all records from each table.
If your data is set up right, you won't find Apples among the Oranges

The following (with "Correct" field names substituted) should give you a
complete list of itemsubclassNAMEs in table 5 and any non-matching names
from higher up the data structure.

I have added a field to indicate the source of the name, but that can be
omitted if you want. I include something similar in most of my UNIONS,
since
it makes them easier to debug/test.

SELECT "Class Name" as TableSource, classNAME As NameEntry FROM
[tblClass]
WHERE ClassName NOT IN (SELECT DISTINCT CorrectClassNameField FROM
tblCategories)

UNION SELECT "Category Name", categoryname FROM [tblCategories]
WHERE categoryname NOT IN (SELECT DISTINCT CorrectCategoryNameField FROM
tblgroup)

UNION SELECT "Group Name", groupNAME FROM [tblgroup]
WHERE groupNAME NOT IN (SELECT DISTINCT CorrectGroupNameField FROM
tblItemClass)

UNION SELECT "Item Class Name", itemclassname FROM [tblItemClass]
WHERE ItemClassName NOT IN (SELECT DISTINCT CorrectItemClassName FROM
tblItemSubClass)

UNION SELECT "Item Subclass Name", itemsubclassNAME FROM
[tblItemsubClass];

--
HTH,
George

I have this SQL code below. It takes 5 tables which for example are
1,2,3,4,5, 5 is subtable of 4, 4 of 3, 3 of 2, and 2 of 1. So when you
open table 1 up it can go to table 5. This code below takes those 5
tables and only finds the unique value in compared table. So lets say:

table 1

auto# Class
1 Ingredients
2 Packaging

Currently the subtable (table 2) has both ingredients and packaging
items together, it would look like this

autonum Category Class
1 Commodities Ingredients
2 Crops Ingredients
3 Dairy Ingredients
4 Label Packaging
5 Resin Packaging
6 Corrugated Packaging
...
17

table 3 or subsubtable would look like, this table has all category
items together, ultimately both ingredient and packaging.

GroupItems Category
1 Oil Commodities
2 Flour Commodities
3 Fruit Crops
4 Nuts Crops
5 Milk Dairy
6 Resin LDPE Resin
7 Promo Label
...
40

The code I should work like this. In table 3 it has Oil which is a
Commodity, so the code should eliminate Commodity out of the returned
query results, and so on for the rest of the items. Here is the code.

SELECT [tblclass].classNAME
FROM [tblClass]
WHERE ClassName NOT IN (SELECT DISTINCT categoryname FROM
tblCategories)
UNION
SELECT [tblCategories].categoryname
FROM [tblCategories]
WHERE categoryname NOT IN (SELECT DISTINCT groupName FROM tblgroup)
UNION
SELECT [tblgroup].groupNAME
FROM [tblgroup]
WHERE groupNAME NOT IN (SELECT DISTINCT ItemClassName FROM
tblItemClass)
UNION
SELECT [tblItemClass].itemclassname
FROM [tblItemClass]
WHERE ItemClassName NOT IN (SELECT DISTINCT ItemSubClassName FROM
tblItemSubClass)
UNION SELECT [tblItemsubClass].itemsubclassNAME
FROM [tblItemsubClass];

Basically it'll take the last most unique record in each table
providing that there is not a subtable that has information for that
one record. Any ideas. currently it just pulls everything all items
from table 1 to 5.

Ryan