Having Clause results minus results from Left join query

Question

Post reply

Having Clause results minus results from Left join query

Lozt-887152

SSC Veteran

Points: 253
More actions
November 15, 2013 at 6:11 am

#285180

Guys,
I would like to combing the following:
1. Retreive the results from SQL 1
2. Retreive the results from SQL 2
3. Remove SQL 2 results from SQL 1 results
4. Select specific rows from the final set of SQL1 results.
Hope this makes sense??? Any help much appreciated.
SQL1
Select col1, col2, count (*)
from table a
where col3 is null
GROUP BY col1, col2,
HAVING count(*) >1
minus
Sql2
select *
from table b
left outer join table c
on b.col1 = c.col1
Many thanks
L

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

the EXCEPT operator can do that, but the number /types of columns must be the same in both queries.

otherwise, show us your real queries instead of pseudocode, and we can get you pointed int he right direction.

Select col1, col2, count (*)

from table a

where col3 is null

GROUP BY col1, col2,

HAVING count(*) >1

EXCEPT

Select col1, col2, count (*)

from table b

left outer join table c

on b.col1 = c.col1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lozt-887152 SSC Veteran Points: 253 More actions · Answer 2

Hi Lowell, thanks for the response. It doesn't appear to work. I get "SQL command not properly ended" error?

FYI

Select l.add, l.phne

from e_lkup l

where mins is null

GROUP BY phne, add

HAVING count(*) >1

EXCEPT

select l.add, l.phne (error on this line)

from s_status ss

left outer join e_lkup l

on l.mcs = ss.mcs

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

This is a SQL Server forum. Are you sure you're not using Oracle?

John

swti6690 SSC Enthusiast Points: 188 More actions · Answer 4

HAVING clause is utilized to riddle the rows subsequent to the entire rows are chosen. It is just similar to a filter. Do not employ HAVING section for any further principles.

[/url]

ScottPletcher SSC Guru Points: 100940 More actions · Answer 5

In SQL Server, you'd need brackets around the name "Add":

Select l.[add], l.phne