I recently posted a question to the forum.  Several people said it was a good example to ask a question this way and suggested I could add it to the FAQ’s.  I was so honored.  Here I post my original question hoping it would contribute a bit to the forum.
---
/* =====================================================================================
--
--  In the following sample database, there are three tables: VENDOR, PRODUCT and
--  PRODUCT_PICTURE.  Please note that Vendor IBM has no product and Product Compaq 4500
--  has no product picture.  Please help to write a join statement which will display
--  the following result:
--
--  Picture_Url        Product_Name        Vendor_Name
--  -----------        ----------------    -----------------
--  A.JPG        DIMENTION 400        DELL
--  B.JPG               DIMENTION 600           DELL
--  null        COMPAQ 4500             HP
--
--  Please note:
--  1.  The order is not important.
--  2.  Vendor IBM has no product, therefore should not be in the result.
--  3.  DIMENTION 600 has three pictures.  We only want DIMENTION 600 to appear once in
--      the result, not three times.  It would be nice that the first picture, B.JPG,
--      will be always picked out.  If not, it is OK.
--  4.  Even though Compaq 4500 has no picture, we still want Compaq 4500 to be included
--      in the result.
--
--  Can this be done using a join? Or it has to be accomplished other way.  If there are
--  more than one way to achieve it, which way is more efficient? Or we definitely have
--  to alter the product_picture table, for example adding a new default_picture column.
--
--  Please use SQL statements executable in Microsoft SQL Server 2000.  Lots of thanks.
-- ===================================================================================== */

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'Test')
    DROP DATABASE Test
go

use Test
go

create database Test
go

create table VENDOR (
   VENDOR_ID        numeric              not null,
   VENDOR_NAME          varchar(20)          not null
)
go

create table PRODUCT (
   PRODUCT_ID           numeric              not null,
   VENDOR_ID        numeric              not null,
   PRODUCT_NAME         varchar(20)          not null
)
go

create table PRODUCT_PICTURE (
   PICTURE_ID           numeric              not null,
   PRODUCT_ID           numeric              not null,
   PICTURE_URL          varchar(20)          not null
)
go

INSERT INTO VENDOR VALUES (1, 'IBM')
INSERT INTO VENDOR VALUES (2, 'DELL')
INSERT INTO VENDOR VALUES (3, 'HP')

INSERT INTO PRODUCT VALUES (1, 2, 'DIMENTION 400')
INSERT INTO PRODUCT VALUES (2, 2, 'DIMENTION 600')
INSERT INTO PRODUCT VALUES (3, 3, 'COMPAQ 4500')

INSERT INTO PRODUCT_PICTURE VALUES (1, 1, 'A.JPG')
INSERT INTO PRODUCT_PICTURE VALUES (2, 2, 'B.JPG')
INSERT INTO PRODUCT_PICTURE VALUES (3, 2, 'C.JPG')
INSERT INTO PRODUCT_PICTURE VALUES (4, 2, 'D.JPG')
go

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum
My FAQ Archive

Email This FAQ To A Friend

My Archive

My FAQ Archive