Posted by Hugo Kornelis on 04/24/06 01:20

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

• Next in forum: Access PErmissions issue
• Prev in forum: Articles on Dynamic SQL and certificates/impersonation
• Thread view: Re: SELECT * FROM "multiple_tables" WHERE "field_(in_those_tables)_have_the_same_paricular_value" ?

[Reply to this message]