Querying against multiple values in associated table 1

[bboffin]

I have two tables:

ITEM
  ItemID INT  (PK)
  AccountID INT
  .
  .

ITEMDESCRIPTION
  ItemID INT      ) (PK)
  Sequence INT    )
  ItemDesc NVARCHAR(255)

Thus there may be multiple rows in ITEMDESCRIPTION for each ITEM record.

I need to find all items for a specific account that have two specific values in any of the corresponding ITEMDESCRIPTION table's ItemDesc field

So far I've come up with as an example:

  SELECT ItemID 
  FROM ITEM I
  WHERE I.AccountID=173 AND
  (SELECT COUNT(*) FROM ITEMDESCRIPTION D1 WHERE D1.ItemID=I.ItemID AND (D1.ItemDesc LIKE '%davidson%')) <> 0 AND
  (SELECT COUNT(*) FROM ITEMDESCRIPTION D2 WHERE D2.ItemID=I.ItemID AND (D2.ItemDesc LIKE '%sarah%')) <> 0

which certainly gets the correct results but I feel that I'm missing something and there should be a better way to do this.

I'm using SQL Server 2005 so any useful extensions in this version may be used. The options of using functions, stored procedures or temporary tables are not open to me unfortunately.

Any ideas?



Bob Boffin

 

[markros]

SELECT I.ItemID FROM ITEM I inner join ItemDetails ID on I.ItemID = ID.ItemID where (ID.ItemDescription like '
%sara%' or ID.ItemDescription like '%davidson%') group by I.ItemID having count(distinct(ID.ItemDetailsID)) >=2

One of the possible solutions.

 

[bboffin]

That's an interesting solution but it doesn't give the same results as my example.

This is probably because of the data. The records in the ItemDescription table are the details of persons living at the same address so if you search on the surname Davidson it is probable that there will be more than one hit for a given item. This will bring the count up to the limit of 2 without necessarily matching the forename of Sarah.

It will also fail if the only ItemDescription record contains both 'Davidson' and 'Sarah'.

The database holds the records of boxes in storage for many different customers and the ItemDescription table in this case is being used to store details of the files inside the box. This usage is peculiar to one customer and so the DB cannot be extended to provide a more useable format.

The client wishes to be able to request retrieval of a box based on the details of a person such as Surname, Forename, Date of Birth and Address all of which are stored in the ItemDescription table.

Can you suggest any other approaches that might work?

Bob Boffin

 

[markros]

I'm not sure I got the exact problem, but if we want to find people with Sarah and Davidson in the Item Description, then you need to use AND, not OR in the above statement.

I believe when I originally read your question I thought of a slightly different approach (SQL Server 2005 and up) using windowing functions. I'll give you my original thought now, then:

;with cte as (SELECT I.ItemID, ID.*, count(*) over (partition by I.ItemID, ID.ItemDescription) as CountDifferentItems, row_number() over (partition by I.ItemID order by ID.ItemDetailID) as RowNum FROM ITEM I inner join ItemDetails ID on I.ItemID = ID.ItemID where (ID.ItemDescription like '%sara%' AND ID.ItemDescription like '%davidson%'))

select * from cte where CountDifferentItems > 1 and RowNum = 1 -- just to pick up one of the records, you may view them all if you remove the second condition

 

[bboffin]

Thanks again. Changing the OR to AND in your original solution didn't help as this requires 'sarah' and 'davidson' to be in the same record. In fact it returned no rows.

The second option is more interesting as it introduces me to some new syntax the OVER .. PARTITION BY clause. Unfortunately it didn't work on the real data.

The solution only needs to return the ItemID where the conditions are true. I don't need to return the actual matching rows. Does that make it easier?

Thanks for all the time you're spending on this. For the moment my original solution is my current way forward so I'll understand if you've had enough of this!

Regards

Bob Boffin

 

[markros]

In my second solution I used AND as well - does changing to OR help?

I'm not yet exactly sure I understood the problem - perhaps you can post your table creation scripts and some input data and desired output - then it would be easier for me to get the problem.

 

[bboffin]

I have to be a bit careful about sending any real data as it relates to the social services department of a London borough. I'll see if I can neutralise a couple of examples and send them to you.

Watch this space...


Regards

Bob Boffin

 

[markros]

I'll check it periodically. Only post the neutral columns and you may want to fake it.

 

[bboffin]

OK. The attached file should create the sample tables and populate them with some sample data where I've blanked out the DOB and Address fields.

Let me know of any problems.


Bob Boffin

 

[markros]

It doesn't let me download this.

 

[bboffin]

Have you tried Right-Click and select 'Save Target As'?


Bob Boffin

 

[markros]

I'm getting security Alert on this message, sorry. I guess I have high Internet security here.

I can try it with FireFox in a minute.