SQL Select * from

Hello,
Each order in my table has more than one note.
I need to select only orders that don't have a note that starts with 'Billing: '

Select order from OrderNotes where text.....

attached is a sample of what the select would look like.
attached highlighted orders should not be included. (Because they have a note that starts with 'Billing: '
The not highlighted is what I need. (I only need the order) --- Example Column 'k'

Your help is appreciated.
Sample.xlsx
W.E.BAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
or to be more precise:

select a.[Order] from OrderNotes a
left join
(
select [Order] from OrderNotes
where [text] like '%Billing%'
group by [Order]
) b
on a.[Order] = b.[Order]
where b.[Order] is null
group by a.[Order]

Open in new window

0
 
Ryan ChongCommented:
quick try:

select a.* from OrderNotes a
left join
(
select [Order] from OrderNotes
where [text] like '%Billing%'
group by [Order]
) b
on a.[Order] = b.[Order]
where b.[Order] is null

Open in new window

0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
<knee-jerk, minute-fast answer>
Select DISTINCT on.order
from OrderNotes on 
   LEFT JOIN (
      SELECT DISTINCT order
      FROM OrderNotes
      where LEFT(text, 8) = "Billing:") onb ON on.Order = onb.Order
WHERE onb.Order IS NULL

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
W.E.BAuthor Commented:
Thank you ,
Ryan Select seems to work, I'm trying it now.
Jim, I'm not getting any results back with your select.

thanks,
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Typo, <> should have been =.   Corrected in original post.
0
 
W.E.BAuthor Commented:
Thank you very much guys.
0
All Courses

From novice to tech pro — start learning today.