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]
