Get the missing records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get the missing records

Author

Topic

kamal1910
Starting Member

3 Posts

Posted - 2011-01-31 : 08:39:17

table: Test1

Id Styleno StyleName
1 001 blue
2 002 red

table: Test2

Id image_id Image_name
1 0001 img1_001
2 0002 img2_001
3 0003 img1_002
4 0004 img2_002

For table Test1 is containg the two style names. Each style name should conatin three images (img1, img2, img3) in Test2 table. But now the table is containg
img1 and img2 for two styles. img3_001 & img3_002 images are missing in Test2. I want to disply the missing images to admin. I want the below result.

ImageName Status
img1_001 Yes
img2_001 Yes
img1_002 Yes
img2_002 Yes
img3_001 No
img3_002 No

Pls help me.

kamalakannan

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-31 : 08:49:47

Show us what you have so far and then we can help you. Not many people on this site will do your homework for you, but all would be more than glad to help you with it after a little effort on your part.

Jim

Everyday I learn something that somebody else already knew

Sachin.Nand

2937 Posts

Posted - 2011-01-31 : 08:51:52

Which table are img3_001 & img3_002 images records saved?

PBUH

kamal1910
Starting Member

3 Posts

Posted - 2011-01-31 : 08:56:03

I will need a report for admin to know what are all the images are missing in table Test2. I am not given the exact scenario, i gave the core of my task. img3_001 & img3_002 images should be in Test2, but it is missing, i will give the below result to admin, and admin know ok...img3_001 & img3_002 images are missing, then they will upload these images into this Table Test2 from application.

ImageName Status
img1_001 Yes
img2_001 Yes
img1_002 Yes
img2_002 Yes
img3_001 No
img3_002 No

kamalakannan

ZZartin
Starting Member

30 Posts

Posted - 2011-01-31 : 18:32:17

Have you tried something with an outer join between table 1 and table 2?

Sachin.Nand

2937 Posts

Posted - 2011-02-01 : 03:31:55

quote:
Originally posted by kamal1910

I will need a report for admin to know what are all the images are missing in table Test2. I am not given the exact scenario, i gave the core of my task. img3_001 & img3_002 images should be in Test2, but it is missing, i will give the below result to admin, and admin know ok...img3_001 & img3_002 images are missing, then they will upload these images into this Table Test2 from application.

ImageName Status
img1_001 Yes
img2_001 Yes
img1_002 Yes
img2_002 Yes
img3_001 No
img3_002 No

kamalakannan

How will the query know that img3_001 & img3_002 are missing when there are no refrences for it ?

PBUH

kamal1910
Starting Member

3 Posts

Posted - 2011-02-01 : 04:04:57

Yes, that is in different table.

Table : Test3

ID ImageName
1 Img1
2 Img2
3 Img3

Based on the above table, we come to know.. Img3 is missing for bi=oth styles numbers 001, 002..

kamalakannan

matty
Posting Yak Master

161 Posts

Posted - 2011-02-01 : 05:59:36

[code]
SELECT t.ImageName,
CASE WHEN t2.Image_name IS NULL THEN 'No' ELSE 'Yes' END AS Status
FROM
(
SELECT a.ImageName + '_' + b.Styleno AS ImageName
FROM Test3 a CROSS JOIN Test1 b
) t
LEFT JOIN Test2 t2 ON t.ImageName = t2.Image_name
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources