Posted by Hugo Kornelis on 04/24/06 23:07

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

• Next in forum: Re: SELECT and regular expression
• Prev in forum: Re: Cannot insert explicit value for identity...
• Thread view: Re: SELECT * FROM "multiple_tables" WHERE "field_(in_those_tables)_have_the_same_paricular_value" ?

[Reply to this message]