Comments
Maximum WHERE Clauses in SQL Query
Written By Scrivs on Jun. 4, 2007.
4 Comments
Report Note
+ Clip This
While working on some code I got to wondering is there a point in a SQL query where you can have too many WHERE clauses and if so how could you counter that? A random example would be say you have 100 friends here on my.9r and we wanted to do a query looking up all of their posts, would you stick in 100 WHERE clauses? I'm thinking there has to be a better way, but nothing comes to mind because over time you are just going to add more friends and therefore the amount of WHERE clauses increases as well.
This is seriously a random example, but applies to something else that I am working on so any help is greatly appreciated.
hthth
Written Jun. 4, 2007 / Report /
I've have very minimal database experience, but I've never heard any mention of too many WHERE clauses.
I'm not entirely sure I understand your dilemma. Venturing a guess, maybe cursors are a potential solution — build a table of the 100 friends, fetch each friend and get his posts? I might be imagining the table structure differently from what it is, but hope this helps.
Scrivs
Written Jun. 4, 2007 / Report /
With your example I would just pull an array with the friend IDs, but let's say I wanted to get all the friends' posts and place them in order for a list. I have a better example, imagine the Stream, but with all your friends data put in it. I can't just do separate pulls of each friend because then they wouldn't be in order unless I pulled them all into an array then went through the array and resorted them by date, which sounds like a pain in the ass.
a_romig
Written Jun. 4, 2007 / Report /
I'd use 1 WHERE clause with an IN in it. That's on a basic level since I don't know the complexity of your database.
SELECT yadda, yidda FROM friends WHERE friend_id IN (id1, id2, id3, etc) ORDER BY yadda;jmathias
Written Jun. 4, 2007 / Report /
IN() is your friend...