4 Table select statement?

Hello can anyone help me out with an sql statement.
I have an order table, which has 4 tables below it, which hold things that a
user can order.
Orders(table)
Then this order table above is linked to the 4 product tables.
ProductA(table)
ProductB(table)
ProductC(table)
ProductD(table)
each of the Product A to B tables have various different fields inside, but
they all share the following 3 fields: OrderID, ProductName, Pic
The product tables are linked by the OrderID field to the Orders Table.
I would like to to write a statement like this:
Select ProductName, Pic from ProductA, ProductB, ProductC, ProductD where
ProductA.OrderID = Orders.OrderID and
ProductB.OrderID = Orders.OrderID and
ProductC.OrderID = Orders.OrderID and
ProductD.OrderID = Orders.OrderID ;
Is the above SQL statement Possible? right now it gives me lots of errors,
and i can't seem to get the right output from that statement, i just wone
non-repeating unique records from all 4 tables , so if i had 8 products in
this order, 2 in each of the product tables, then i want to display 8 record
s
only, right now if i play around this statement i get lots and lots of
duplicate records.ARTMIC wrote:
> Hello can anyone help me out with an sql statement.
> I have an order table, which has 4 tables below it, which hold things that
a
> user can order.
> Orders(table)
> Then this order table above is linked to the 4 product tables.
> ProductA(table)
> ProductB(table)
> ProductC(table)
> ProductD(table)
>
> each of the Product A to B tables have various different fields inside, bu
t
> they all share the following 3 fields: OrderID, ProductName, Pic
> The product tables are linked by the OrderID field to the Orders Table.
> I would like to to write a statement like this:
> Select ProductName, Pic from ProductA, ProductB, ProductC, ProductD where
> ProductA.OrderID = Orders.OrderID and
> ProductB.OrderID = Orders.OrderID and
> ProductC.OrderID = Orders.OrderID and
> ProductD.OrderID = Orders.OrderID ;
> Is the above SQL statement Possible? right now it gives me lots of errors,
> and i can't seem to get the right output from that statement, i just wone
> non-repeating unique records from all 4 tables , so if i had 8 products in
> this order, 2 in each of the product tables, then i want to display 8 reco
rds
> only, right now if i play around this statement i get lots and lots of
> duplicate records.
It seems like you want a UNION rather than a join. Here's a guess:
SELECT orderid, productname, pic
FROM
(SELECT A.orderid, A.productname, A.pic
FROM producta AS A
UNION ALL
SELECT B.orderid, B.productname, B.pic
FROM productb AS B
UNION ALL
SELECT C.orderid, C.productname, C.pic
FROM productc AS C
UNION ALL
SELECT D.orderid, D.productname, D.pic
FROM productd AS D) AS T
WHERE ... /* ? not specified */
ORDER BY orderid ;
This looks more than a little strange. Why orderid in the products
tables? And why isn't there a common products table for the common
columns? Maybe I've misunderstood, in which case the best way for you
to get more help is to post DDL and sample data and show your required
end results. DDL and sample data means CREATE TABLE statements for your
tables and INSERT statements for some example data (just the essential
columns will do). Don't forget to include keys and constraints with
your DDL.
Hope this helps.
David Portas
SQL Server MVP
--|||Hello David, thank you for the answer,
i will play with the joins that you mentioned,
i think i didn't express my question the right way, hard to explain,
the 4 product tables, are really orderDetail tables. So in reality ProductA
is really DetailA, DetailB, DetailC, DetailD which is linked to the order
table.
the reason for the above is becuase the uesr will only purchase 4 types of
things... and each thing has their own data fields. ( this setup is for a
manufactuerer)
It is a master-detail type of situation with Order header, and order detail,
but in my case, i have 4 order Detail tables, and i want to display all the
details from the 4 tables in one sql statement, at least the common fields..
.
I woudl have gone with a flat table for the detail table, but iwould have
had like 250+ fields in the table, that is too much to manage...so i split
the order-detail table into 4 distinct productsDetail tables... now i am
paying for it :)
i will play with your sql statements and hopefully it will get me over my
proplem, i will reply if it worked or not,
Thanks again !
"David Portas" wrote:

> ARTMIC wrote:
>
> It seems like you want a UNION rather than a join. Here's a guess:
> SELECT orderid, productname, pic
> FROM
> (SELECT A.orderid, A.productname, A.pic
> FROM producta AS A
> UNION ALL
> SELECT B.orderid, B.productname, B.pic
> FROM productb AS B
> UNION ALL
> SELECT C.orderid, C.productname, C.pic
> FROM productc AS C
> UNION ALL
> SELECT D.orderid, D.productname, D.pic
> FROM productd AS D) AS T
> WHERE ... /* ? not specified */
> ORDER BY orderid ;
> This looks more than a little strange. Why orderid in the products
> tables? And why isn't there a common products table for the common
> columns? Maybe I've misunderstood, in which case the best way for you
> to get more help is to post DDL and sample data and show your required
> end results. DDL and sample data means CREATE TABLE statements for your
> tables and INSERT statements for some example data (just the essential
> columns will do). Don't forget to include keys and constraints with
> your DDL.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||ARTMIC wrote:
> I woudl have gone with a flat table for the detail table, but iwould have
> had like 250+ fields in the table, that is too much to manage...so i split
> the order-detail table into 4 distinct productsDetail tables... now i am
> paying for it :)
>
I would prefer 5 tables. 4 for each unique product type. 1 for the
attributes that are common to all types (name for example).
David Portas
SQL Server MVP
--|||Thank you David,
you are right, i might as well just move the common fields into an
OrderDetail table and then link to the 4 OrderDetailProd tables.
Thanks,
"David Portas" wrote:

> ARTMIC wrote:
> I would prefer 5 tables. 4 for each unique product type. 1 for the
> attributes that are common to all types (name for example).
> --
> David Portas
> SQL Server MVP
> --
>