best way to check a record has certain entries in a field

Forum Moderators: open

Message Too Old, No Replies

best way to check a record has certain entries in a field

lethal0r

11:52 am on Oct 10, 2008 (gmt 0)

I need to check if a widget exists in a table with both a widget_attribute_id of 1 AND a widget_attribute_id of 2.

what is the best way to do so?

at the moment I am doing

SELECT * FROM (
SELECT COUNT(DISTINCT widget_attribute_id) AS widget_attribute_count FROM widgets WHERE (widget_attribute_id = 1 OR widget_attribute_id = 2))
AS matching_attributes WHERE widget_attribute_id > 1

mark_roach

2:46 pm on Oct 10, 2008 (gmt 0)

Join the table with itself.

Select a.widget from widget_table a, widget_table b where a.widget_attribute_id=1 and b.widget_attribute_id=2 and a.widget=b.widget

lethal0r

12:34 pm on Oct 11, 2008 (gmt 0)

thanks thats very handy.

can you also do that where the relationship a.widget=b.widget is more complex?

eg where the records would be joined using other tables.