INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Effective Forum Participation

An example to ask questions by 9295
Posted: 7 Dec 04

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 Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close