SQL QUERY

Question

Post reply

SQL QUERY

chawra

SSC Enthusiast

Points: 138
More actions
December 30, 2003 at 1:01 pm

#101830

SQL-SERVER (one table)
trck# Pol#
----------------------------------
A-001-111 123-123-01
A-001-111 123-123-01
A-001-111 123-123-03
A-001-111 123-123-04
A-001-111 123-123-05
A-001-112 123-124-05
A-001-112 123-124-05
B-001-111 112-124-05
B-001-111 112-124-05
C-001-001 111-111-11
C-001-001 111-111-11
B-001-003 001-111-11
B-001-003 001-000-11
G-001-001 111-234-001
I need to write SQL which will give all values where Trak# is same but any of the pol# is different. i.e result should be
trck# Pol#
----------------------------------
A-001-111 123-123-01
A-001-111 123-123-01
A-001-111 123-123-03
A-001-111 123-123-04
A-001-111 123-123-05
B-001-003 001-111-11
B-001-003 001-000-11
Thanx

Viewing 10 posts - 1 through 9 (of 9 total)

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

I do not completely understand

Please clarify.

Once you understand the BITs, all the pieces come together

chawra SSC Enthusiast Points: 138 More actions · Answer 2

I need to pull those records from a table X where pol#'s are different for single trck#.

Single trck#(A-001-111) has multiple pol#'s(123-123-01/03/04/05).

pol# and trck# are two columns

trck# = A-001-111

pol# = 123-123-01

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

Is the result set you have in the initial post complete?

I do not get why "B-001-003, 001-111-11" IS in your result set while "B-001-111 112-124-05" is not.

Once you understand the BITs, all the pieces come together

chawra SSC Enthusiast Points: 138 More actions · Answer 4

because both the pol#'s(112-124-05) are same... I'm looking for those values where pol#'s are different for same trck#.

trck#, pol#

B-001-111, 112-124-05

ThomasH SSCertifiable Points: 7437 More actions · Answer 5

Something like...

Select trck#, Pol#

From [Table]

Group by trck#, Pol#

Having count(*) = 1

Once you understand the BITs, all the pieces come together

Jonathan SSC-Insane Points: 20427 More actions · Answer 6



SELECT o.Trck#, o.Pol#

FROM Chawra O JOIN

(SELECT Trck#

 FROM

 (SELECT DISTINCT Trck#, Pol#

  FROM Chawra) c

GROUP BY Trck#

HAVING COUNT(*) > 1) x ON x.Trck# = o.Trck#

--Jonathan

noeld SSC Guru Points: 96590 More actions · Answer 7

SELECT

TrackNmbr

, PolNmbr

From

Table

Group by

TrackNmbr

, PolNmbr

Having Count(*)=1

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 8

WOW!!

That was funny. Three replies almost a once

* Noel

chawra SSC Enthusiast Points: 138 More actions · Answer 9

chawra

SSC Enthusiast

Points: 138

December 30, 2003 at 2:31 pm

#488281

Jonathan,

PERFACT.....

Thanx