CODEWITHSUNDEEP
sqldb2distinct
madprofessor
madprofessor

Reputation: 47

fetching just one row from a type

I have one table ticket and other table log. There are many logs for each row in ticket. I want to know if certain string is in some log and then fetch one register from ticket it doesn't matter which one, because I just want to get one row per ticket. Here is what I have at the moment:

SELECT ticket.id,ticket.description,ticket.owner 
FROM ticket
JOIN log
ON (log.ticketid=ticket.id)
where log.description LIKE '%someparameter%'

What should I do in this case?

Upvotes: 2

Views: 212

Answers (3)

Leo
Leo

Reputation: 1514

The usual DB2 equivalent to LIMIT 1 is FETCH FIRST 1 ROWS ONLY:

SELECT ticket.id,ticket.description,ticket.owner 
FROM ticket
JOIN log
ON (log.ticketid=ticket.id)
where log.description LIKE '%someparameter%'
FETCH FIRST 1 ROWS ONLY

You can also enable the LIMIT syntax via:

db2set DB2_COMPATIBILITY_VECTOR=4000

Or:

db2set DB2_COMPATIBILITY_VECTOR=MYS

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

SELECT
  *
FROM
  ticket
WHERE
  EXISTS (SELECT *
            FROM log
           WHERE log.ticketid = ticket.id
             AND log.description LIKE '%someparameter%'

Or...

SELECT
  ticket.id, ticket.description, ticket.owner 
FROM
  ticket
INNER JOIN
  log
    ON (log.ticketid=ticket.id)
WHERE
  log.description LIKE '%someparameter%'
GROUP BY
  ticket.id, ticket.description, ticket.owner

Upvotes: 1

cduffin
cduffin

Reputation: 210

You could try using the SQL Limit option?

SELECT ticket.id,ticket.description,ticket.owner 
FROM ticket
JOIN log
ON (log.ticketid=ticket.id)
where log.description LIKE '%someparameter%'
LIMIT 1;

Upvotes: 2

Related Questions