Cedric
> Subject: Difficult SQL Question
> From: "Pascal Peters" <[EMAIL PROTECTED]>
> Date: Wed, 2 Jun 2004 10:31:02 +0200
> Thread:
http://www.houseoffusion.com/cf_lists/index.cfm/method=messages&threadid=32925&forumid=4#165218
>
> If your db supports it, you can use INTERSECT
>
> SELECT TicketID
> FROM fields
> WHERE FieldID = 1
> AND Value = '5'
> INTERSECT
> SELECT TicketID
> FROM fields
> WHERE FieldID = 2
> AND Value = 'Hello'
>
> Alternatively you could use group by (supposing (TicketID, FieldID) is
> unique)
>
> SELECT TicketID
> FROM fields
> WHERE 0 = 1
> OR (FieldID = 1 AND Value = '5')
> OR (FieldID = 2 AND Value = 'Hello')
> GROUP BY TicketID
> HAVING COUNT(TicketID) = 2
>
> You can also use subqueries
> SELECT *
> FROM tickets
> WHERE 0 = 0
> AND TicketID IN (
> SELECT TicketID
> FROM fields
> WHERE FieldID = 1
> AND Value = '5'
> )
> AND TicketID IN (
> SELECT TicketID
> FROM fields
> WHERE FieldID = 2
> AND Value = 'Hello'
> )
>
> I didn't want to type to much, so there are some modifications you
> should make to the code:
> - Use <cfqueryparam> everywhere
> - Don't use SELECT * but specify the columns you want
> - In the group by example COUNT() has to be the number of fields you
> want to search on
>
> > -----Original Message-----
> > From: Cedric Villat [mailto:[EMAIL PROTECTED]
> > Sent: woensdag 2 juni 2004 2:01
> > To: CF-Talk
> > Subject: Difficult SQL Question
> >
> > Ok, I have a bit of a problem with some SQL I'm trying to
> > build. I have a table of Tickets, and then a table with a
> > list of "fields" that are associated with tickets. Here are
> > some values for the "fields" table:
>
> > What I want to do is select the TicketID's that have a
> > "(FieldID = 1 AND Value LIKE 'Hello') AND (FieldID = 2 AND
> > Value = 5)". In this case I would want Ticket #1 returned.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
