Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL Select * from

Posted on 2016-09-09
Medium Priority
53 Views
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
0
Question by:W.E.B
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 2
• 2
• 2

LVL 53

Expert Comment

ID: 41791548
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
``````
0

LVL 53

Accepted Solution

Ryan Chong earned 1000 total points
ID: 41791551
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]
``````
0

LVL 66

Assisted Solution

Jim Horn earned 1000 total points
ID: 41791552
``````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
``````
0

Author Comment

ID: 41791592
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

LVL 66

Expert Comment

ID: 41791596
Typo, <> should have been =.   Corrected in original post.
0

Author Closing Comment

ID: 41791604
Thank you very much guys.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month6 days, left to enroll

#### 688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.