August 19, 2009 at 2:30 pm
Hi,
I have a few tables that relate to each other in a many-many type of situation. Items, Images and a table to relate them. Each Item can have many images and some images show more than one item. Each image has "Type" associated with it to keep track of what the image actually shows. Each Item eventually needs a "BarCode" type of image, and my mission is to pull out all the items that don't already have a "BarCode" associated with them.
This code can be used to re-build a sample of my data:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Images]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Images]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Items]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Items]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Relationships]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Relationships]
GO
CREATE TABLE [dbo].[Images] (
[ImageID] [int] IDENTITY (1, 1) NOT NULL ,
[Filename] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FileType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Items] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Relationships] (
[RelationshipID] [int] IDENTITY (1, 1) NOT NULL ,
[ImageID] [int] NULL ,
[ItemID] [int] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT Images ON
INSERT INTO Images
(ImageID, Filename, FileType)
SELECT '1','ABC123_BC.jpg','BarCode' UNION ALL
SELECT '2','ABC123_A.jpg','Alternate' UNION ALL
SELECT '3','ABC123_B.jpg','Alternate' UNION ALL
SELECT '4','DEF456.jpg','MainImage' UNION ALL
SELECT '5','DEF789_BC.jpg','BarCode' UNION ALL
SELECT '6','DEF123_MI.jpg','MultipleItems'
SET IDENTITY_INSERT Images OFF
SET IDENTITY_INSERT Items ON
INSERT INTO Items
(ItemID, ItemNumber)
SELECT '1','ABC123' UNION ALL
SELECT '2','ABC456' UNION ALL
SELECT '3','ABC789' UNION ALL
SELECT '4','DEF123' UNION ALL
SELECT '5','DEF456' UNION ALL
SELECT '6','DEF789'
SET IDENTITY_INSERT Items OFF
SET IDENTITY_INSERT Relationships ON
INSERT INTO Relationships
(RelationshipID, ItemID, ImageID)
SELECT '1','1','1' UNION ALL
SELECT '2','1','2' UNION ALL
SELECT '3','1','3' UNION ALL
SELECT '4','5','4' UNION ALL
SELECT '5','6','5' UNION ALL
SELECT '6','4','6' UNION ALL
SELECT '7','5','6' UNION ALL
SELECT '8','2','6'
My question is how can I know which items don't already have a "BarCode".
The tables are linked as such:
select * from Items, Images, relationships
where relationships.ItemID = Items.ItemID
and relationships.ImageID = Images.ImageID
I would like to run a query that only returns items that don't have an associated image marked "BarCode".
Of course, this doesn't produce the desired results because Item ABC123 does have a "BarCode" entry:
select distinct ItemNumber from Items, Images, relationships
where relationships.ItemID = Items.ItemID
and relationships.ImageID = Images.ImageID
and Images.FileType != 'BarCode'
Any help is appreciated.
Dave
August 19, 2009 at 2:51 pm
Thanks for posting the sample data!!! It made my job MUCH simpler. 🙂
The following solution uses explicit JOINS because your use of the WHERE clause with a list of tables is being deprecated. First, it uses a CTE (BarcodeItems) to define the set of ItemIDs that have a barcode. Then the main query simply tests each itemID for a match with the barcoded items. Any matches are omitted by the WHERE NOT EXISTS.
Let me know if you have any questions.
--
;with BarcodeItems as
(select items.ItemID as BarcodeItemId
from Items
join Relationships on relationships.ItemID = Items.ItemID
join Images on relationships.ImageID = Images.ImageID
where Filetype = 'BarCode')
--
select *
from Items
join Relationships on relationships.ItemID = Items.ItemID
join Images on relationships.ImageID = Images.ImageID
where not exists (select 1 from BarcodeItems where Items.ItemID = BarcodeItemID)
order by Items.ItemID,[FileName]
--
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 19, 2009 at 2:55 pm
With the sample data, it's a lot easier to help.
Here's how I would do it, first get the items you do not want to show, and left join on the subquery, to get only the products you want to show.
SELECT *
FROM Items i
LEFT JOIN (SELECT r.itemID
FROM Relationships r
INNER JOIN Images Im
ON r.ImageID = im.ImageID
WHERE im.FileType = 'BarCode') AS UnwantedItems
ON i.ItemID = UnwantedItems.ItemID
WHERE UnwantedItems.itemID IS NULL
Hope that helps,
Cheers,
J-F
August 19, 2009 at 3:04 pm
Hey J-F,
Your subquery solution is cleaner in that it avoids the uneccessary join to the item table to find the unwanted ItemIDs. The CTE solution should be changed as follows.
--
;with BarcodeItems as
(select ItemID as BarcodeItemId
from Relationships
join Images on relationships.ImageID = Images.ImageID
where Filetype = 'BarCode')
--
select *
from Items
join Relationships on relationships.ItemID = Items.ItemID
join Images on relationships.ImageID = Images.ImageID
where not exists (select 1 from BarcodeItems where Items.ItemID = BarcodeItemID)
order by Items.ItemID,[FileName]
--
One other point to note: The subquery solution includes Item Number ABC789 because of the LEFT JOIN, while the CTE solution omits ABC789 because it fails the JOIN between the Items table and the Relationships table.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 20, 2009 at 6:34 am
Hi Bob,
You're right about the query, it fails to join when there is no match between the items table and the relationships, it all depends on what the requirements are for this scenario.
Both queries work, but I'm only starting to get used to write cte, so I do not find it obvious in that scenario. It all comes down to readability IMO.
Cheers,
J-F
August 20, 2009 at 11:00 am
Thanks to you both for your input. I have used J-F's solution for no other reason than I understand it.
The sample tables that I provided were just that - a stripped down version of my actual data. I had to modify the code to work correctly with my program so I needed to use the one that I understood.
Thanks again for your help!
Dave
August 20, 2009 at 11:18 am
Happy it helps, and thanks a lot for the feedback.
Have a nice day,
Cheers,
J-F
August 20, 2009 at 11:41 am
Guys, I encourage you to give CTEs a try. They act like views which exist only for the duration of a single query. But they read from top to bottom, instead of inside out like subqueries. Some people find that more intuitive and it certainly makes comments more readable, as they can be step-by-step.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply