May 12, 2008 at 9:32 am
Hi all,
I have an Equipment table with the columns EquipID and EquipTypeID (amongst others). I then have other tables for different equipment types with more type specific detail in them.
I want to search for Equipment based on information in the detail tables, i.e. I want to search using a keyword and pull back all equipment, regardless of type, that has data in columns that match the keyword.
I have two ways in which I think I can do this at the moment and was looking for some advice as to which way to go.
1) I can write a procedure that searches the equip table and inner joins to a detail table for a type, then union that onto the same again linking to each of the detail tables in turn where the detail.column(s) like '%keyword%'.
2) I can add a keyword column to the equipment table and add a trigger to each of the detail tables updating the keywords when detail is changed. I can then search just on the equipment table.
Any advice on the above, or alternatives would be greatly appreciated.
Thanks in advance,
Al.
May 12, 2008 at 12:29 pm
Don't use a trigger for this.
If you post the DDL or even some sample DDL, we can help you qith the query.
I don't think you need a union, you probably just need some creative outer joins.
May 13, 2008 at 2:03 am
Hi Michael,
An example of the sql I would use is....
--Computers
SELECT dei.EquipItemID, dei.EquipTypeID, dei.EquipFolderID, dei.IconID, dei.ImageID, dei.IsDeleted
FROM dbo.Dat_EquipItems dei
INNER JOIN dbo.Ref_EquipTypes ret
ON dei.EquipTypeID = ret.EquipTypeID
AND ret.Name = 'Computer'
LEFT JOIN dbo.Dat_EquipComputer deqc
ON dei.EquipItemID = deqc.EquipItemID
WHERE deqc.Model LIKE '%' + @Keywords + '%'
OR deqc.SerialNumber LIKE '%' + @Keywords + '%'
UNION
--Phones
SELECT dei.EquipItemID, dei.EquipTypeID, dei.EquipFolderID, dei.IconID, dei.ImageID, dei.IsDeleted
FROM dbo.Dat_EquipItems dei
INNER JOIN dbo.Ref_EquipTypes ret
ON dei.EquipTypeID = ret.EquipTypeID
AND ret.Name = 'Phone'
LEFT JOIN dbo.Dat_EquipPhone deqc
ON dei.EquipItemID = deqc.EquipItemID
WHERE deqc.Model LIKE '%' + @Keywords + '%'
OR deqc.SerialNumber LIKE '%' + @Keywords + '%'
OR deqc.Number LIKE '%' + @Keywords + '%'
UNION
etc......
Thanks,
Al.
May 13, 2008 at 4:44 am
Here are two options. The first is just using left joins and is relatively simple. Because your LIKE starts with a wildcard, you will end up with table scans on your main table and all sub tables.
The second option is using sub-queries (I put them into CTE's just to make it look nice). This may perform better if the optimizer decides to do the work of looking up the sub-table values and then materializes the results so it does not have to scan the main table. Or, it may also just do table scans. You will have to look at the execution plans to determine which works better for you.
[font="Courier New"]SELECT
dei.EquipItemID
, dei.EquipTypeID
, dei.EquipFolderID
, dei.IconID
, dei.ImageID
, dei.IsDeleted
FROM
dbo.Dat_EquipItems dei
LEFT JOIN dbo.Dat_EquipComputer Comp ON dei.EquipItemID = Comp.EquipItemID
AND (Comp.Model LIKE '%' + @Keywords + '%' OR Comp.SerialNumber LIKE '%' + @Keywords + '%')
LEFT JOIN dbo.Dat_EquipPhone Phone ON dei.EquipItemID = Phone.EquipItemID
AND (Phone.Model LIKE '%' + @Keywords + '%' OR Phone.SerialNumber LIKE '%' + @Keywords + '%' OR Phone.Number LIKE '%' + @Keywords + '%')
WHERE
Comp.EquipItemID IS NOT NULL
OR Phone.EquipItemID IS NOT NULL
; WITH Comp (EquipItemID)
AS (SELECT EquipItemID FROM dbo.Dat_EquipComputer WHERE Model LIKE '%' + @Keywords + '%' OR SerialNumber LIKE '%' + @Keywords + '%')
, Phone (EquipItemID)
AS (SELECT EquipItemID FROM dbo.Dat_EquipPhone WHERE Model LIKE '%' + @Keywords + '%' OR SerialNumber LIKE '%' + @Keywords + '%' OR Number LIKE '%' + @Keywords + '%')
SELECT
dei.EquipItemID
, dei.EquipTypeID
, dei.EquipFolderID
, dei.IconID
, dei.ImageID
, dei.IsDeleted
FROM
dbo.Dat_EquipItems dei
LEFT JOIN Comp ON Comp.EquipItemID = dei.EquipItemID
LEFT JOIN Phone ON Phone.EquipItemID = dei.EquipItemID
WHERE
Comp.EquipItemID IS NOT NULL
OR Phone.EquipItemID IS NOT NULL[/font]
May 13, 2008 at 5:02 am
Hi Michael,
Great stuff, thanks for that.
Just one final question if that's ok? I entertained the trigger method earlier so that the stored procedure would not have to be modified as new equipment types were introduced into the database/application. As such, I could unit test the stored procedure/trigger routine and it would be pretty much self contained as any new types created would just update the keyword columns with their own triggers, but I would not have to change the searching procedure in any way.
Since you mentioned not to use triggers, I have browsed the web and come across a number of reasons not to use them, supporting your suggestion, but the chance to self contain this procedure still appeals.
In your opinion, does not using triggers, but altering the procedure when new equipment types are introduced (possibly making quite a large stored procedure) still seem the right way forward?
Thanks again for your help so far,
Al.
May 13, 2008 at 5:48 am
Your goal should be to normalize the data. If you have to keep your child records and search temrs in sync (trigger or another method) you end up essentially storing the same piece of information twice. This should be avoided.
Even with a trigger, a new item type for your design seems to require a new child table. This is ok and ends up somewhat like inheritance in your model, but does require a schema change. With a trigger, you would have to create the table and the new trigger - this is a more invasive change (in my opinion) than modifying a select statement in a stored procedure.
If you wanted to avoid these changes altogether, you could normalize your data more and create a table that stores "Search Terms" or some kind of user-defined field that has a foreign key to your child tables. Unless you get new items constantly (which would be a problem in your design anyway), I would tend to avoid this and do something simple like you are already doing. Anything that requires a new table or column is going to require some planning and testing, don't be too afraid of it.
May 13, 2008 at 6:28 am
Hi Michael,
Thanks for all your help, after reading that I'll go ahead and use the sql you have suggested in your earlier post.
Best regards,
Al.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply