Newbie- help with UNION clause

I have a parent table for whom there are repeating children plus occasional
oddball children. Rather than create massive child table with the same item
over and over for each parent, I am trying to build a parent to 2 children
relationship like this
Parent
ID Name FKID
1 A -1
2 A 1
3 A 2
Child1
ID Code
1 Alpha
2 Beta
3 Gamma
Child2
ID Code
1 One
2 Two
3 Three
The results should be a union of 2 joins,
Name Code
A Alpha
A Beta
A Gamma
A One
A Two
A Three
I think I need a UNION that will
1) test if the FKID is -1
2) if so select all records from Child1
3) UNION them with a JOIN to Child2
The part I'm having trouble with is the test for -1. Can someone give me an
idea of the syntax?
Thanks!I do not fully understand the request. Try:
select a.[name], b.code
from (select [name] from Parent where fkid = -1) as a cross join child1 as b
union all
select a.[name], b.code
from (select [name] from Parent where fkid = -1) as a cross join child2 as b
AMB
"Coffee guy" wrote:

> I have a parent table for whom there are repeating children plus occasiona
l
> oddball children. Rather than create massive child table with the same it
em
> over and over for each parent, I am trying to build a parent to 2 children
> relationship like this
> Parent
> ID Name FKID
> 1 A -1
> 2 A 1
> 3 A 2
> Child1
> ID Code
> 1 Alpha
> 2 Beta
> 3 Gamma
> Child2
> ID Code
> 1 One
> 2 Two
> 3 Three
> The results should be a union of 2 joins,
> Name Code
> A Alpha
> A Beta
> A Gamma
> A One
> A Two
> A Three
> I think I need a UNION that will
> 1) test if the FKID is -1
> 2) if so select all records from Child1
> 3) UNION them with a JOIN to Child2
> The part I'm having trouble with is the test for -1. Can someone give me
an
> idea of the syntax?
> Thanks!|||Your table names seem confusing. The "parent" table in a relationship is the
table referenced by a foreign key, not the other way around. The usual
metaphor is ONE parent to MANY children. On the other hand I could be
misunderstanding because you didn't include any DDL to clarify keys and
constraints for us.
It seems like this may be what you want:
SELECT P.name, COALESCE(C1.code, C2.code) AS code
FROM Parent AS P
LEFT JOIN Child1 AS C1
ON P.fkid = -1
LEFT JOIN Child2 AS C2
ON C2.id = P.fkid
I can't say I like this design much. Why two referenced tables instead of
one? If the relationship between P and C1 is really many-to-many then I
suggest you create a Joining table rather than compromise the (presumably
undeclared) foreign key in the P table.
David Portas
SQL Server MVP
--|||Here's an interpretation (based on my assumption that this is a many-to-many
relationship) of what this data might look like in Third Normal Form. Note
the foreign keys.
CREATE TABLE foo (foo_id INTEGER PRIMARY KEY, name VARCHAR(10) NOT NULL
UNIQUE)
CREATE TABLE bar (bar_id INTEGER PRIMARY KEY, code VARCHAR(10) NOT NULL
UNIQUE)
CREATE TABLE foobar (foo_id INTEGER NOT NULL REFERENCES foo (foo_id), bar_id
INTEGER NOT NULL REFERENCES bar (bar_id), PRIMARY KEY (bar_id, foo_id))
INSERT INTO foo (foo_id, name)
SELECT 1, 'A'
INSERT INTO bar (bar_id, code)
SELECT 1, 'One' UNION ALL
SELECT 2, 'Two' UNION ALL
SELECT 3, 'Three' UNION ALL
SELECT 4, 'Alpha' UNION ALL
SELECT 5, 'Beta' UNION ALL
SELECT 6, 'Gamma'
/* Joining table */
INSERT INTO foobar (foo_id, bar_id)
SELECT 1,4 UNION ALL
SELECT 1,5 UNION ALL
SELECT 1,6 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,2
Your query could then be:
SELECT F.name, B.code
FROM foo AS F
JOIN foobar AS J
ON F.foo_id = J.foo_id
JOIN bar AS B
ON B.bar_id = J.bar_id
David Portas
SQL Server MVP
--|||Dave this is what I was trying to avoid, having to repeat the "repeating"
child keys. Let me try a simple verbal example:
We have an inventory of cars in a new car lot. There are several Option
Packages consisting of xx options. Most cars have one or more option
packages. Plus some have additional onesy-twosy options
Option
1 Power steering \
2 Power windows -- this is a group of options that most all have, call it
group A
3 Air conditioning /
4 CD Player
5 GPS display
Now our car list
Car 1 has Group A
Car 2 has Group A plus option 4
Car 3 has Group A plus option 4 and 5
Car 4 has Group A
I would like a query (stored procedure) that will return the list of options
for a given car. Since the contents of group A may change, I would like to
avoid your suggestion of a tertiary or "joining" table as it will have to
list all options for every car. This is not "normalized" in my view as we
have repeating data.
Thanks for spending the time with your example, I was able to run it first
try!
"David Portas" wrote:

> Here's an interpretation (based on my assumption that this is a many-to-ma
ny
> relationship) of what this data might look like in Third Normal Form. Note
> the foreign keys.
> CREATE TABLE foo (foo_id INTEGER PRIMARY KEY, name VARCHAR(10) NOT NULL
> UNIQUE)
> CREATE TABLE bar (bar_id INTEGER PRIMARY KEY, code VARCHAR(10) NOT NULL
> UNIQUE)
> CREATE TABLE foobar (foo_id INTEGER NOT NULL REFERENCES foo (foo_id), bar_
id
> INTEGER NOT NULL REFERENCES bar (bar_id), PRIMARY KEY (bar_id, foo_id))
> INSERT INTO foo (foo_id, name)
> SELECT 1, 'A'
> INSERT INTO bar (bar_id, code)
> SELECT 1, 'One' UNION ALL
> SELECT 2, 'Two' UNION ALL
> SELECT 3, 'Three' UNION ALL
> SELECT 4, 'Alpha' UNION ALL
> SELECT 5, 'Beta' UNION ALL
> SELECT 6, 'Gamma'
> /* Joining table */
> INSERT INTO foobar (foo_id, bar_id)
> SELECT 1,4 UNION ALL
> SELECT 1,5 UNION ALL
> SELECT 1,6 UNION ALL
> SELECT 1,1 UNION ALL
> SELECT 1,2
> Your query could then be:
> SELECT F.name, B.code
> FROM foo AS F
> JOIN foobar AS J
> ON F.foo_id = J.foo_id
> JOIN bar AS B
> ON B.bar_id = J.bar_id
> --
> David Portas
> SQL Server MVP
> --
>
>|||What I need is a single select (a stored procedure) that will
If parent.fkid = -1 select * from child1
plus
select * from child2 where parent.fkid = child2.ID
Sorry if I was not clear.
"Alejandro Mesa" wrote:
> I do not fully understand the request. Try:
> select a.[name], b.code
> from (select [name] from Parent where fkid = -1) as a cross join child1 as b
> union all
> select a.[name], b.code
> from (select [name] from Parent where fkid = -1) as a cross join child2 as b
>
> AMB
>
> "Coffee guy" wrote:
>|||Actually Alejandro your suggestion works perfectly!
I have a lot to learn, thank you!
"Alejandro Mesa" wrote:
> I do not fully understand the request. Try:
> select a.[name], b.code
> from (select [name] from Parent where fkid = -1) as a cross join child1 as b
> union all
> select a.[name], b.code
> from (select [name] from Parent where fkid = -1) as a cross join child2 as b
>
> AMB
>
> "Coffee guy" wrote:
>