December 19, 2012 at 1:51 pm
If i had 4 tables:
FrogsTable: NamesColumn:bill,steve,john
DogsTable: NamesColumn: bob, bill, john
catsTable: NameColumn: steve, sam, ax
AnimalsNamedJohnTable:
AnimalsColumn: Frogs, Dogs, Cats
NamedJohnColumn: True, True, False
how can I run a check to see if I have any frogs named john and update the animalsNamedJohn table's frogs row to True or (0)?
I esentailly want to check all my tables that have the same column name for a specific value and have a different table record wether that value exists for that table. Please help. Thanks
December 19, 2012 at 2:39 pm
Nicholas, is this for a school project? It kind of seems that way, but it is such a terrible design that I would hate to think an instructor is teaching this somewhere. Alas, I'll let Celko elaborate on that. To answer your question, you should start by providing sample table create statements and data, like the below, so we can more easily help you.
USE tempdb
GO
IF OBJECT_ID('tempdb..FrogsTable','u') IS NOT NULL
DROP TABLE tempdb..FrogsTable;
IF OBJECT_ID('tempdb..DogsTable','u') IS NOT NULL
DROP TABLE tempdb..DogsTable;
IF OBJECT_ID('tempdb..CatsTable','u') IS NOT NULL
DROP TABLE tempdb..CatsTable;
IF OBJECT_ID('tempdb..AnimalsNamedJohnTable','u') IS NOT NULL
DROP TABLE tempdb..AnimalsNamedJohnTable;
CREATE TABLE tempdb..FrogsTable
(
NamesColumn VARCHAR(20)
);
CREATE TABLE tempdb..DogsTable
(
NamesColumn VARCHAR(20)
);
CREATE TABLE tempdb..CatsTable
(
NamesColumn VARCHAR(20)
);
CREATE TABLE tempdb..AnimalsNamedJohnTable
(
AnimalsColumn VARCHAR(20),
NamedJohnColumn BIT
);
GO
INSERT INTO tempdb..FrogsTable
VALUES ('bill'),('steve'),('john');
INSERT INTO tempdb..DogsTable
VALUES ('bob'),('bill'),('john');
INSERT INTO tempdb..CatsTable
VALUES ('steve'),('sam'),('ax');
INSERT INTO tempdb..AnimalsNamedJohnTable
VALUES ('Frogs',0),('Dogs',1),('Cats',0);
Notice I have set the NamedJohnColumn = 0 for 'frogs', eventhough you have indicated it should be one. We need to make sure our update statement is working right? There are probably several ways you can do this, so here is one.
-- verify the 'frogs' row = 0
SELECT * FROM tempdb..AnimalsNamedJohnTable
WHERE AnimalsColumn = 'frogs'
UPDATE tempdb..AnimalsNamedJohnTable
SET NamedJohnColumn = CASE WHEN EXISTS (SELECT 1 FROM tempdb..FrogsTable
WHERE NamesColumn = 'john')
THEN 1
ELSE 0
END
WHERE AnimalsColumn = 'frogs';
-- verify the 'frogs' row = 1
SELECT * FROM tempdb..AnimalsNamedJohnTable
WHERE AnimalsColumn = 'frogs'
Now, like I mentioned above, your design is terrible. What happens if your situation dictates you start tracking mice, squirrels, chickens, and 100 other animals? Are you going to create a new table for each one? You will be far better served by having a table to store the types of animals, then a table to store the animal data, including name and animaltypeid, like the below.
IF OBJECT_ID('tempdb..AnimalTypes','u') IS NOT NULL
DROP TABLE tempdb..AnimalTypes;
IF OBJECT_ID('tempdb..Animals','u') IS NOT NULL
DROP TABLE tempdb..Animals;
CREATE TABLE tempdb..AnimalTypes
(
AnimalTypeID INT IDENTITY(1,1),
AnimalType VARCHAR(20) NOT NULL
);
CREATE TABLE tempdb..Animals
(
AnimalID INT IDENTITY(1,1),
AnimalTypeID INT NOT NULL,
AnimalName VARCHAR(20) NOT NULL
);
INSERT INTO tempdb..AnimalTypes (AnimalType)
VALUES ('cat'),('dog'),('frog');
INSERT INTO tempdb..Animals (AnimalTypeID, AnimalName)
VALUES ((SELECT AnimalTypeID FROM tempdb..AnimalTypes WHERE AnimalType = 'cat'),'steve'),
((SELECT AnimalTypeID FROM tempdb..AnimalTypes WHERE AnimalType = 'cat'),'sam'),
((SELECT AnimalTypeID FROM tempdb..AnimalTypes WHERE AnimalType = 'cat'),'ax'),
((SELECT AnimalTypeID FROM tempdb..AnimalTypes WHERE AnimalType = 'dog'),'bob'),
((SELECT AnimalTypeID FROM tempdb..AnimalTypes WHERE AnimalType = 'dog'),'bill'),
((SELECT AnimalTypeID FROM tempdb..AnimalTypes WHERE AnimalType = 'dog'),'john'),
((SELECT AnimalTypeID FROM tempdb..AnimalTypes WHERE AnimalType = 'frog'),'bill'),
((SELECT AnimalTypeID FROM tempdb..AnimalTypes WHERE AnimalType = 'frog'),'steve'),
((SELECT AnimalTypeID FROM tempdb..AnimalTypes WHERE AnimalType = 'frog'),'john')
GO
SELECT
*
FROM tempdb..Animals
Now, if you want to see if you have frogs named 'john', it is a simple query...
SELECT
a.AnimalType,
b.AnimalName
FROM tempdb..AnimalTypes a INNER JOIN tempdb..Animals b
ON a.AnimalTypeID = b.AnimalTypeID
WHERE a.AnimalType = 'frog'
AND b.AnimalName = 'john'
Of course, you might want to do a cross tab, or a group by to see the break down of animal types and names, but you should work on understanding why your design needs improving first.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 19, 2012 at 3:14 pm
Greg, Thank you I see exactly what you are saying.
December 19, 2012 at 3:20 pm
I would have to agree with Greg. This design is horrible. You really need to read up on normalization. This whole collection of tables could be a single table. The way you have put this together is going to drive you insane as this application gets larger. It will become more and more painful.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply