SQL Query to compare multiple rows

Question

I'm new here, and new to SQL. I have searched, but I can't seem to find an answer to my question. Maybe you gurus can help. I have a table that has customer ID numbers and their status (number) among other things. For instance, a few lines would be like this:

Status - ACCTnum - CustName - City - State

95 - A330 - Billy Burger - Cleveland - Oh
11 - A330/Q - Billy Burger Store#2 - Cleveland - Oh
15 - B250 - Spanky - Columbus - Oh
15 - B250/Z - Spanky#2 - Springfield - OH
15 - B250/Y - Spanky#3 - Miami - FL

We see here, there is a main account number, and a sub account number, but they occupy the same field. Account A330 is billy burger, and his second store is A330/Q. The status column is for their salesman number. If the number is 95, it is a dead account. The problem is, for our purposes, the status of a main account cannot be dead if a sub account is in good standing. So what I need is a query that can basically select any records that meet the criteria: "If ACCTnum is status 95, and has sub accounts that are not status 95"

Ideally if I ran the query on the table above it should return the first two records, since A330 is status 95 and A330/Q is not. It should ignore the other records.

I have tried the INTERSECT command with no success (I assume because it only works for two different tables?). I am a total SQL n00b, be gentle ;)

You need a self join.

Shiva
– Shiva

2013-12-02 23:40:06 +00:00
Commented Dec 2, 2013 at 23:40 — Shiva
– Shiva, Commented Dec 2, 2013 at 23:40

Joel Coehoorn · Accepted Answer · 2013-12-03 14:22:47Z

1

select distinct c1.*
from Customers c1
inner join Customers c2 ON c2.ACCTnum LIKE c1.ACCTnum + '%'
where c1.ACCTnum.status = 95 and c2.ACCTnum <> 95

edited Dec 3, 2013 at 14:22

answered Dec 2, 2013 at 23:46

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3059028 Over a year ago

Thanks for the tip. I get the following error though: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'Where'.

Joel Coehoorn Over a year ago

whoops. that should have been ON. Fixed in the answer now.

user3059028 Over a year ago

I know it says not to use the comment box for "Thanks". But "Thanks" ;). I ended up doing it slightly different, but you pointed me in the right direction with the Join.

Collectives™ on Stack Overflow

SQL Query to compare multiple rows

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related