Welcome to WebmasterWorld Guest from 23.22.46.195

Forum Moderators: open

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

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

5+ Year Member



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

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

10+ Year Member



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

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

5+ Year Member



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.

 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month