Subquery with Select Substring

[Andy79]

Hello,

Basic question here, how to I add the second SQL as a subquery into
the first to get all the data i need in one output? If the substring
part doesnt find any data with %cam% or %cgs% I would want the row to
be shown and record NULL in the 'Contract_Only' column

Thanks for your help!

query1
SELECT ticket, tktDate, comment, nett, gross, grnCode, optCode,
poolOption, payCode, payName
FROM tbl.Rpts_Tickets
WHERE (optCode = 'PE')

query2
SELECT id, SUBSTRING(comment, 1, 7) AS Contract_Only
FROM tbl.Rpts_Tickets
WHERE (comment LIKE '%cam%') OR (comment LIKE '%cgs%')


many thanks for your help!
Andy

 

[Andy79]

Sorted it:


SELECT     tbl.Rpts_Tickets.ticket, tbl.Rpts_Tickets.tktDate,
tbl.Rpts_Tickets.comment, tbl.Rpts_Tickets.nett,
tbl.Rpts_Tickets.gross, tbl.Rpts_Tickets.grnCode,
tbl.Rpts_Tickets.optCode,
tbl.Rpts_Tickets.poolOption, .tbl.Rpts_TicketspayCode, .tbl.Rpts_Tickets.payName,
derivedtbl_1.Contract_Only
FROM         tbl.Rpts_Tickets LEFT OUTER JOIN

( SELECT     id, SUBSTRING(comment, 1, 7) AS Contract_Only
FROM         tbl.Rpts_Tickets AS tbl.Rpts_Tickets_1
WHERE     (comment LIKE '%cam%') OR (comment LIKE '%cgs%')) AS
derivedtbl_1 ON tbl.Rpts_Tickets.id = derivedtbl_1.id

WHERE     (tbl.Rpts_Tickets.optCode = 'PE')