SQL join queries

jrattinger
jrattinger used Ask the Experts™
on
Hi,

i need help writting a sql query that will select rows only when more that one occurance of the value in a column occurs. in my case, i need to select rows when there is more than one occurance of the same tracking number.

So if i had a small database with a table called billing with 6 rows and the value of the column "tracking number" was as follows (1, 2, 3, 2, 3, 3), i would want the query to return 5 rows - 2,3,2,3,3.

Additionally, i need another query that does just the opposite - returns 1 row - 1.

I've tried several attemplts at inner joins without much luck.

Thanks,
John
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:

select x.*
(
select c.*, rn = row_number() over (partition by tracking_numer)
from billing
) x
where rn > 1

select x.*
(
select c.*, rn = row_number() over (partition by tracking_numer)
from billing
) x
where rn =1
Sr. System Analyst
Commented:
here:

select * from myTable
where id in (select id from myTable group by id having count(1)>1)
Rajkumar GsSoftware Engineer
Commented:
Here I demonstrated with temporary tables and your sample data. This contains the two queries
CREATE TABLE #table
(
	TrackingNo INT
)

INSERT INTO #table
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3


-- Query 1
SELECT * FROM #table WHERE TrackingNo IN 
(
SELECT TrackingNo  FROM #table
GROUP BY TrackingNo
HAVING COUNT(TrackingNo) > 1
)

-- Query 2
SELECT * FROM #table WHERE TrackingNo IN 
(
SELECT TrackingNo  FROM #table
GROUP BY TrackingNo
HAVING COUNT(TrackingNo) = 1
)

DROP TABLE #table

Open in new window


Raj
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

HainKurtSr. System Analyst

Commented:
my demo :)


with myTable as(
select 1 id union all
select 2 union all 
select 3 union all
select 2 union all
select 3 union all
select 3
)
select * from myTable 
where id in (select id from myTable group by id having count(1)>1) 

id
2
3
2
3
3

Open in new window

Rajkumar GsSoftware Engineer

Commented:
:)

Hainkurt, I didn't see your query as it was not posted when I start tying.

Raj

Author

Commented:
Awesome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial