Need help on writing a join script 2

[9295]

/* =====================================================================================
--
-- 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

 

[donutman]

Let me congratulate you on posting the most clear and complete question that I've ever seen of this type. This solution is one that uses a correlated subquery. It isn't the fastest, but I'm sure someone else is working on one with a derived table.

[Blue]SELECT[/Blue] Picture_URL[Gray],[/Gray] Product_Name[Gray],[/Gray] Vendor_Name
   [Blue]FROM[/Blue] Vendor V [Blue]INNER[/Blue] [Blue]JOIN[/Blue] Product P 
      [Blue]ON[/Blue] V.Vendor_Id[Gray]=[/Gray]P.Vendor_Id
   [Fuchsia]Left[/Fuchsia] [Blue]JOIN[/Blue] Product_Picture PP
      [Blue]ON[/Blue] P.Product_Id[Gray]=[/Gray]PP.Product_Id [Gray]AND[/Gray]
         PP.Picture_Id[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]MIN[/Fuchsia][Gray]([/Gray]Picture_Id[Gray])[/Gray] 
                           [Blue]FROM[/Blue] Product_Picture 
                           [Blue]WHERE[/Blue] PP.Product_Id[Gray]=[/Gray]Product_Id[Gray])[/Gray]

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Derived table approach:

[Blue]SELECT[/Blue] PP3.Picture_Url[Gray],[/Gray] Product_Name[Gray],[/Gray] Vendor_Name
   [Blue]FROM[/Blue] Vendor V [Blue]INNER[/Blue] [Blue]JOIN[/Blue] Product P 
      [Blue]ON[/Blue] V.Vendor_Id[Gray]=[/Gray]P.Vendor_Id
   [Fuchsia]Left[/Fuchsia] [Blue]JOIN[/Blue] 
      [Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]MIN[/Fuchsia][Gray]([/Gray]PP1.Picture_Id[Gray])[/Gray] Picture_Id[Gray],[/Gray] PP1.Product_Id
          [Blue]FROM[/Blue] Product_Picture PP1 [Blue]INNER[/Blue] [Blue]JOIN[/Blue] 
             Product_Picture PP2 
          [Blue]ON[/Blue] PP1.Picture_Id[Gray]<[/Gray][Gray]=[/Gray]PP2.Picture_Id [Gray]AND[/Gray]
             PP1.Product_Id[Gray]=[/Gray]PP2.Product_Id
          [Blue]GROUP[/Blue] [Blue]BY[/Blue] PP1.Product_Id[Gray])[/Gray] PP
      [Blue]ON[/Blue] PP.Product_Id[Gray]=[/Gray]P.Product_Id
   [Fuchsia]Left[/Fuchsia] [Blue]JOIN[/Blue] Product_Picture PP3
      [Blue]ON[/Blue] PP.Product_Id[Gray]=[/Gray]PP3.Product_Id [Gray]AND[/Gray]
         PP.Picture_Id[Gray]=[/Gray]PP3.Picture_Id

I'm not so sure that this would be faster with a large table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[9295]

Dear Donutman,

First of all, I have to thank you for your quick response and kind remarks. I always try to make my question clear to everyone.

Your two solutions work great. They both produce the right answer instantly. Because I have only small testing data, I can’t tell which way is better though.

Your answers are most clear and complete as well. I didn’t have to do any edit at all- simply copy, paste and get the answer.

Your answers are very smart solutions. I haven’t heavily used SQL for a long time. My SQL skill is rusty (even commands and syntax are different now.) Do you have any good SQL books you can recommend?

Your help is deeply appreciated.

9295

 

[donutman]

Try this link to the FAQ's of this forum:

http://www.tek-tips.com/faq.cfm?pid=183

and scroll down to SQL Server Resources and SQL Server Reference Material.

Good luck,
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

Wow I'm impressed. That is definitely the best way to ask a question I've seen yet. You should make an FAQ using this as an example of how to ask a question.

BTW, even though it works without a default picture column, you might consider adding one because the first picture isn't always the best one to show. Of course it depends on how you get you pictures. We get them from logged in users and allow them to chosse which picture they want dsiplayed. If they don't choose, the first picture is the default. If you are importing pictures form some other source in a large group, this is clearly impractical though.

Questions about posting. See faq183-874

 

[ESquared]

Just be aware that if you put conditions on the picture, you'll need to be careful where it goes. For example, in the derived table method the where clause would belong inside the first derived table. In the correlated subquery method, it would belong probably in the JOIN clause of the PP table, but might work in the WHERE clause of the main query (I'd have to do testing to be sure). Oh, actually, it would depend on what kind of criteria you were using, because it might need to go in the correlated subquery as well.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[donutman]

Eric, no need to send the email regarding your testing. I now understand how you were cross-referencing your tests with vongrunts. It appears that the various approaches have their niche. I'm posting here because, the derived table (DT) approach here has a lot more work (<= join) to do (with a larger table) in relation to the correlated subquery (CS) as opposed to the DT and CS used in your tests. Do you think it would be worth testing it to see if CSs have a niche or are you confident that the DT approach will still rule?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

I think more testing is warranted, I'm just confused about the results I got. Could it be because the production server has two CPUs and in one case I got a CPU that was busy and in another case I got one that was free?

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[9295]

Karl (donutman),

I ordered two books, The Guru's Guide to Transact-SQL and The Guru's Guide to SQL Server Stored Procedures, XML, and HTML based my search in Amazon.com before I saw your message. I am glad that these two books are in your list. I did make right choices. Thanks a lot.

Tom (9295)

 

[donutman]

I've heard good things about The Guru's Guide to T-SQL. I think you'll like it.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Maybe I should get some books! I would love to learn some things I don't know about SQL and SQL Server.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[donutman]

I suspect that in a year or two, you'll be authoring them (assuming you get you act together). Say, did you ever get your black belt?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

wish id read that script at the top properly before running it - my old test database has gone - good job it didnt have too much of interest in it !!

[blue]DBomrrsm[/blue]

 

[donutman]

Ouch!

 

[ESquared]

Yep... black belt now, although I hurt my knee (and reinjured it in class sparring, twice) so I can't spar or do certain things until it heals *completely*, probably in six months.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[donutman]

Ouch to you too!

 

[9295]

SQLSister,

Thank you for your kind compliments and suggestion. I felt so honored. I have just posted my original question to FAQ's. Hope it would help.

Tom