SELECT * FROM "multiple_tables" WHERE "field_(in_those_tables)_have_the_same_paricular_v alue" ?

[andro]

Hi everybody!

I have several tables from which I want to exract the SAME value (along with
other referenced data).
All the values are in the same column within the tables.
How can I achieve this?

TIA.
Andro
*************************
example: (piping bill of material where tables represent piping systems)

TBL1-water
pos. item type size rating
---------------------------------
01 flange 3" 300#
02 valve wafer 2" 150#
03 valve ball 1" 150#
04 elr90 2"

TBL2-oil
(similar like above).........etc.
----------------------------------

how to select (say) BALL VALVES from these tables along with pos.,size,
rating etc.?

[Hugo Kornelis]

On Mon, 24 Apr 2006 00:04:40 +0200, andro wrote:

Hi everybody!

I have several tables from which I want to exract the SAME value (along with
other referenced data).
All the values are in the same column within the tables.
How can I achieve this?

TIA.
Andro
*************************
example: (piping bill of material where tables represent piping systems)

TBL1-water
pos. item type size rating
---------------------------------
01 flange 3" 300#
02 valve wafer 2" 150#
03 valve ball 1" 150#
04 elr90 2"

TBL2-oil
(similar like above).........etc.
----------------------------------

how to select (say) BALL VALVES from these tables along with pos.,size,
rating etc.?

Hi Andro,

SELECT 'water' AS system, pos, item, type, size, rating
FROM TBL1_water
WHERE type = 'ball'
UNION ALL
SELECT 'oil' AS system, pos, item, type, size, rating
FROM TBL2_oil
WHERE type = 'ball'
UNION ALL
etc

But a better solution would be to use just one table, with water/oil/etc
as extra column (part of the primary key), instead of splitting the data
over several similar tables.

--
Hugo Kornelis, SQL Server MVP

[andro]

Hugo !

Thank you very much for your help.
(I've noticed that such a simple question is not so simple to resolve with
basic SQL knowledge such as mine).
It is difficult to use first approach by entering everytime TBL_NAME (system
here).

Is there any easier way - by using table names as "parameters"?

I like your second approach.
Would you be more specific about "extra column (***part of the primary
key***)"
What do you mean by "part of the primary key"?

Thank you.
"Hugo Kornelis" <hu**@perFact.REMOVETHIS.info.INVALID> wrote in message
news:f9********************************@4ax.com...

On Mon, 24 Apr 2006 00:04:40 +0200, andro wrote:

Hi everybody!

I have several tables from which I want to exract the SAME value (along
with
other referenced data).
All the values are in the same column within the tables.
How can I achieve this?

TIA.
Andro
*************************
example: (piping bill of material where tables represent piping systems)

TBL1-water
pos. item type size rating
---------------------------------
01 flange 3" 300#
02 valve wafer 2" 150#
03 valve ball 1" 150#
04 elr90 2"

TBL2-oil
(similar like above).........etc.
----------------------------------

how to select (say) BALL VALVES from these tables along with pos.,size,
rating etc.?

Hi Andro,

SELECT 'water' AS system, pos, item, type, size, rating
FROM TBL1_water
WHERE type = 'ball'
UNION ALL
SELECT 'oil' AS system, pos, item, type, size, rating
FROM TBL2_oil
WHERE type = 'ball'
UNION ALL
etc

But a better solution would be to use just one table, with water/oil/etc
as extra column (part of the primary key), instead of splitting the data
over several similar tables.

--
Hugo Kornelis, SQL Server MVP

[Hugo Kornelis]

On Mon, 24 Apr 2006 00:46:02 +0200, andro wrote:

Hugo !

Thank you very much for your help.
(I've noticed that such a simple question is not so simple to resolve with
basic SQL knowledge such as mine).
It is difficult to use first approach by entering everytime TBL_NAME (system
here).
Hi Andro,

I'm sorry, I don't understand this part of your message.

Is there any easier way - by using table names as "parameters"?
There is, but it's not recommended, because of the security
implications. If you want to read about the method *and* the risks it
has, go to http://www.sommarskog.se/dynamic_sql.html.

I like your second approach.
Would you be more specific about "extra column (***part of the primary
key***)"
What do you mean by "part of the primary key"?

That would be easier to answer if you had told me exactly how yoour
tables currently look.

I'll use a made-up example to illustrate this. Consider the following
(bad!) design for daily sales data:

CREATE TABLE North_Sales
(SaleDate smalldatetime NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products
)
CREATE TABLE East_Sales
(SaleDate smalldatetime NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products
)
(and two more for regions South and West)

These four tables can and should be replaced by this single table:

CREATE TABLE Sales
(SaleDate smalldatetime NOT NULL,
Region char(5) NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, Region, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products,
CHECK (Region IN ('North', 'South', 'East', 'West'))
)

Notice how I added a column "Region", _AND* added this column to the
list of columns that make up the primary key.

(I also added a check constraint - this should be replaced by a FOREIGN
KEY constraint if there's a Regions table in the database as well).

--
Hugo Kornelis, SQL Server MVP

[andro]

Hugo!

Thanx again for your help.
Regarding the first part.....nothing special - it's just that I'm kinda
bored to repeatedly enter nearly the same query for every system here.
(There are not just several but say 40-50 which are changing from project to
project.)
I just wondered (please note: I'm a newbie) is there a way to instruct the
system in a kind of "loop" DO - UNTIL or FOR/NEXT for every system table in
database to repat the qurey within the tables using "parameter" TBL_NAME
where neccessary. Some kind of automation maybe - you know. (Seems very
advanced request).
(I heaven't checked your link yet ).

Second part of yours is advanced to me.
Seems that I have to try harder from now on.

Thank you for your time.
"Hugo Kornelis" <hu**@perFact.REMOVETHIS.info.INVALID> wrote in message
news:kr********************************@4ax.com...

On Mon, 24 Apr 2006 00:46:02 +0200, andro wrote:

Hugo !

Thank you very much for your help.
(I've noticed that such a simple question is not so simple to resolve with
basic SQL knowledge such as mine).
It is difficult to use first approach by entering everytime TBL_NAME
(system
here).

Hi Andro,

I'm sorry, I don't understand this part of your message.

Is there any easier way - by using table names as "parameters"?

There is, but it's not recommended, because of the security
implications. If you want to read about the method *and* the risks it
has, go to http://www.sommarskog.se/dynamic_sql.html.

I like your second approach.
Would you be more specific about "extra column (***part of the primary
key***)"
What do you mean by "part of the primary key"?

That would be easier to answer if you had told me exactly how yoour
tables currently look.

I'll use a made-up example to illustrate this. Consider the following
(bad!) design for daily sales data:

CREATE TABLE North_Sales
(SaleDate smalldatetime NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products
)
CREATE TABLE East_Sales
(SaleDate smalldatetime NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products
)
(and two more for regions South and West)

These four tables can and should be replaced by this single table:

CREATE TABLE Sales
(SaleDate smalldatetime NOT NULL,
Region char(5) NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, Region, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products,
CHECK (Region IN ('North', 'South', 'East', 'West'))
)

Notice how I added a column "Region", _AND* added this column to the
list of columns that make up the primary key.

(I also added a check constraint - this should be replaced by a FOREIGN
KEY constraint if there's a Regions table in the database as well).

--
Hugo Kornelis, SQL Server MVP

[andro]

> That would be easier to answer if you had told me exactly how yoour

tables currently look.

Tables look like this:
*************************
example: (piping bill of material where tables represent piping systems)

TBL1-water
pos. item type size rating operation pcs. material
identification_no ( <-----unique ! )
-----------------------------------------------------------------------------------
01 flange 3" 300# NULL 2 AISI316L
012324585
02 valve wafer 2" 150# hydraulic 3 CS/SS
065898329
03 valve ball 1" 150# manual 1 BZ/BZ
378987548
04 elr90 2" NULL 8 CS
879539287
05 etc......

TBL2-oil
(similar like above).........etc.

TBL3-gas
---------------

identification_no is unique across the tables (same item type has one)

[--CELKO--]

>> .. repeat the qurey within the tables using "parameter" TBL_NAME
where neccessary. <<

You need to change your ENTIRE MENTAL MODEL. You are still thinking in
terms of a file system model of data. A table models a set of one and
only one kind of entity. Thus passing a table name to a procedure is
totally wrong. Your procedure would lack any coupling and cohesion.
It would be named the "Britney Spears, Squids and/or Automobiles"
procedure, so generic as to be unmaintainable.

The design flaw Hugo showed you is called "attribute splitting" -- a
table is split up on the values of an attribute.

[Hugo Kornelis]

On Mon, 24 Apr 2006 01:55:24 +0200, andro wrote:

That would be easier to answer if you had told me exactly how yoour
tables currently look.

Tables look like this:

Hi Andro,

Thanks for giving an example, but it doesn't tell me enough. Could you
please post the DDL (CREATE TABLE statements, including all constraints
and indexes) for the tables?

Also, you write:
TBL1-water
pos. item type size rating operation pcs. material
identification_no ( <-----unique ! )
I'm not sure if I interpret the uniqueness of the identification_no
correct. (Please bear in mind that Ennlgish is not my native language
and that I know nothing about piping systems - most of the terms in your
example are Greek to me!)

I assume that it's impossible to have the same identification_no on two
rows in table TBL1-water. But can the same identification_no still be in
one of the other tables? And if so, what restrictions hold for the other
columns? Or to use a concrete example: given this population of
TBL1-water:
TBL1-water
pos. item type size rating operation pcs. material
identification_no ( <-----unique ! )
-----------------------------------------------------------------------------------
01 flange 3" 300# NULL 2 AISI316L
012324585
02 valve wafer 2" 150# hydraulic 3 CS/SS
065898329
03 valve ball 1" 150# manual 1 BZ/BZ
378987548
04 elr90 2" NULL 8 CS
879539287
05 etc......
Would this population of TBL2-oil be possible:
TBL2-oil
pos. item type size rating operation pcs. material
identification_no ( <-----unique ! )
-----------------------------------------------------------------------------------
01 foo bar 2" 200# automatic 7 KG/H33C
477396411
02 flange wafer 2" 250# electric 3 CS/SS
378987548
03 etc......

Note that I copied identification_no 378987548 over to the second table,
but I changed all the other columns. Is this a vallid example? If not,
why not? Just because the same identification_no is in both tables, or
because it is in two tables with different columns? And should ALL
columns be the same, or just some? Which? What should I correct at
minimum in TBL2-oil to make this an allowed example?

With the answers to those questions, I can probably help you a lot
further along the way.

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

andro (av*@email.t-com.hr) writes:

Regarding the first part.....nothing special - it's just that I'm kinda
bored to repeatedly enter nearly the same query for every system here.
(There are not just several but say 40-50 which are changing from
project to project.) I just wondered (please note: I'm a newbie) is
there a way to instruct the system in a kind of "loop" DO - UNTIL or
FOR/NEXT for every system table in database to repat the qurey within
the tables using "parameter" TBL_NAME where neccessary. Some kind of
automation maybe - you know. (Seems very advanced request).
You can do this:

exec sp_MSforeachtable 'SELECT COUNT(*), ''?'' FROM ?'

But this is mainly good for admin tasks. And it is undocumented and
unsupported.

The reason that there is not any more civilised way to this is that
it is not meaningful. In a well-designed database, each table is
supposed to describe a unique entity, and thus each table has a
different layout and running the same query on several tables is not
possible, unless it's a trivial one like the SELECT COUNT(*) in the
example above.

If you have a database where you have a lot of tables with the same
structure, then you have a poorly designed database. Best would be
to unite the tables into one - or construct a partitioned view over
the tables, and then run the queries against that view.
(I heaven't checked your link yet ).

In case you have multiple tables wthe same layout, look particularly
at http://www.sommarskog.se/dynamic_sql.html#Sales_yymm.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[av1]

I accept that this is bad database design.
(Any help toward improving it is appreciated, TIA)
But this is a "real world" example.

Designers are working on project consisting of several piping systems
(say 30) preparing (separate) bills of materials (BOMs) for each system
(water, oil, fuel, steam etc). BOMs looks similar to "TBL1_water" shown
at the beginning.
Later on - the project manager wants to know how many similar items we
have accross the project for purchasing purposes (discounts). He wants
to group similar items say ball valves or flanges for bidding purposes.
Regarding the table look - they use primarily Excel ( ** don't blame me
here ! ** )
I know that we need more advanced tool. That's why I raised this
question here.
Anyway, we can use this *.xls files more or less efficiently. (not to
mention problems with inconsistency with naming conventions, formats
etc. which exists ).
Question still remains: how to SELECT similar data of interest across
many tables?

Regarding the "identification_no": this data will be populated later on
after retrieving the data of interest (in question above), selecting
the supplier and after purchasing the items so it is not important in
the initial stage.
It is unique mark for the same item and same type across all the tables
(Say
ball valve with size 1", rated pressure 150#, manually operated and
made of bronze has assigned it's own identification_no (at
will) across the all tables. Similar valve type (ball) with all the
items the same except one (say different material ) has different
indent_no).
Take a shoe store example:
Maker Model size color ident_no
---------------------------------------------------------------------------
Clarks XY 11 black 1
Clarks XY 11 grey 2
Clarks XY 12 grey 3

etc...
(database guru would not prepare the table like this - this is only for
interpretation)

Generally, I assume that all the tables should be kept in one table
with one additional distinct column data representing the "system" at
every corresponding row.

[av1]

Sorry - I forgot

What would be the syntax to include additional column data representing
the "system" in a combined table ?

[Erland Sommarskog]

(av*@email.t-com.hr) writes:

Question still remains: how to SELECT similar data of interest across
many tables?

What would be the syntax to include additional column data representing
the "system" in a combined table ?

SELECT system = 'system of tbl1', col1 ,col2, col2,
FROM tbl1
WHERE ...
UNION ALL
SELECT 'system2, col1, col2, col3,
FROM tbl2
WHERE ...
UNION ALL

If the WHERE clause is generally applicable, you can do:

SELECT system, co1l, col2, col3, ...
FROM (SELECT system = 'system of tbl1', col1 ,col2, col2,
FROM tbl1
UNION ALL
SELECT 'system2, col1, col2, col3,
FROM tbl2
UNION ALL
...) AS x
WHERE ...

Here I'm using a derived table, which logically a temp table within
the query, but not necessarily comnputed as such. That is the optimizer
may recast the computation order for better effeciency.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On 25 Apr 2006 03:41:43 -0700, av*@email.t-com.hr wrote:

I accept that this is bad database design.
(Any help toward improving it is appreciated, TIA)
But this is a "real world" example.

Designers are working on project consisting of several piping systems
(say 30) preparing (separate) bills of materials (BOMs) for each system
(water, oil, fuel, steam etc). BOMs looks similar to "TBL1_water" shown
at the beginning.
Later on - the project manager wants to know how many similar items we
have accross the project for purchasing purposes (discounts). He wants
to group similar items say ball valves or flanges for bidding purposes.
Regarding the table look - they use primarily Excel ( ** don't blame me
here ! ** )
I know that we need more advanced tool. That's why I raised this
question here.
Anyway, we can use this *.xls files more or less efficiently. (not to
mention problems with inconsistency with naming conventions, formats
etc. which exists ).
Question still remains: how to SELECT similar data of interest across
many tables?
Hi Andro,

And the answer still remains that this is VERY easy if you use just one
table instead of 30 tables for the 30 piping systems.

In my previous message, I asked you to post the DDL (CREATE TABLE
statements, including all constraints and indexes) for the tables that
you are currently using. If you had done that, I could now have given
you the CREATE TABLE statement for the single table to replace your 30
current tables.
Regarding the "identification_no": this data will be populated later on
after retrieving the data of interest (in question above), selecting
the supplier and after purchasing the items so it is not important in
the initial stage.
It is unique mark for the same item and same type across all the tables
(Say
ball valve with size 1", rated pressure 150#, manually operated and
made of bronze has assigned it's own identification_no (at
will) across the all tables. Similar valve type (ball) with all the
items the same except one (say different material ) has different
indent_no).
Okay. So what exactly does this mean for the sample population for two
of the current tables that I posted? Is that particular combination of
vallues logically permitted? If not, what column(s) need to be changed
and why?
Generally, I assume that all the tables should be kept in one table
with one additional distinct column data representing the "system" at
every corresponding row.

Yes.

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Amit (co***********@gmail.com) writes:

I am able to retrive the uncommon columns from two similar tables, but
the problem is i require a query, which can help me to find out which
columns are added separately. Another query which can say which columns
are deleted. And the last one can say which column has changed .

It is not clear if you are looking for metadata information or data. But
I assumed that you want to compare the metadata for two tables.

This may get you started:

select a.tblname, a.colname, a.typename, a.max_length,
b.tblname, b.colname, b.typename, b.max_length
from (SELECT tblname = o.name, colname = c.name,
typename = t.name, c.max_length
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON t.system_type_id = c.system_type_id
AND t.user_type_id <= 255
WHERE o.name = 'tbl1') AS a
full join
(SELECT tblname = o.name, colname = c.name,
typename = t.name, c.max_length
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON t.system_type_id = c.system_type_id
AND t.user_type_id <= 255
WHERE o.name = 'tbl2') AS b
ON a.colname = b.colname
AND a.typename = b.typename
AND coalesce(a.max_length, 0) = coalesce(b.max_length, 0)
WHERE a.colname IS NULL OR b.colname IS NULL

This query is for SQL 2005. If you are on SQL 2000, the query is similar,
but you need to use sysobjects, syscolumns and systypes, and somewhat
different colunm names. You could look that up in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx