CODEWITHSUNDEEP
sqlnested
Brad8118
Brad8118

Reputation: 4702

SQL - Nested select statement?

I have 2 tables, ticket and ticket_custom.
Here are how the tables are set up.alt text

I have a web interface where I can change the state (value in the table ticket_custom) The web interface adds a new entry instead of updating the original entry. 

ticket  name    value
1   state   Ready for Final Verification
2   state   Ready for Final Verification
1   state   Verified

The last row is added

So I need to modify the queries.

   SELECT p.value AS __color__,
   id AS ticket, summary, component, version, c.value AS state, milestone, t.type AS type, 
   owner, status,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t,  ticket_custom c
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' AND id = c.ticket
  WHERE status <> 'closed' AND id = c.ticket
  ORDER BY CAST(p.value AS int), milestone, t.type, time, c.ticket

The query now returns both entries. I tried to add a nested select in the where clause. 

SELECT g.ticket
FROM ticket_custom g
WHERE g.ticket = id
ORDER BY g.ticket DESC LIMIT 1

So -

SELECT p.value AS __color__,
   id AS ticket, summary, component, version, c.value AS state, milestone, t.type AS type, 
   owner, status,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t,  ticket_custom c
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' AND id = c.ticket
  WHERE status <> 'closed' AND id = c.ticket and (
         SELECT g.ticket
         FROM ticket_custom g
         WHERE g.ticket = id
         ORDER BY g.ticket DESC LIMIT 1 )
  ORDER BY CAST(p.value AS int), milestone, t.type, time, c.ticket

Clearly I'm doing something wrong.

Upvotes: 0

Views: 7919

Answers (3)

Steven
Steven

Reputation: 3928

Assuming at least one ticket, you could do this instead:

SELECT MAX(g.ticket) FROM ticket_custom g WHERE g.ticket=id

You can convert that to a JOIN or nested SELECT etc.

so change id=c.ticket id=MAX(c.ticket)

but check your WHERE & LEFT JOIN there's some strange stuff in there.

Upvotes: 1

Indolent Coder
Indolent Coder

Reputation: 728

Don't make your database schema dynamic (i.e. "table-driven"). It isn't a sensible design pattern (I know from experience). It saves only a little effort upfront in creating additional attributes, but incurs additional overhead thereafter, both in writing queries and in the database processing required to execute them.

Just add a state id column to the ticket table and update it when the state changes. Create a reference table to store the state names. Then your queries become quite simple, and requires less effort for database engine to do an indexed join to a small reference table.

Upvotes: 0

Jeremy
Jeremy

Reputation: 6670

This appears to be more of a design issue than a query issue. While it is possible to return only a single record from the ticket_custom table there is not a way to determine which record that will be. In you sample table ticket 1 has a value of "Ready for..." and "Verified" but nothing that shows which happened first. Logically, the "Ready for..." happened first.

The easiest way for you to fix this issue would be to add a timestamp or incrementing ID field. Then your subquery could select the most recent entry (latest timestamp or highest ID).

Upvotes: 2

Related Questions