ansaurus

Question

Answer 1

+1 A:

Try

select *
from tblTagGlass ttg
inner join tblTagGlass ttgC ON
ttg.JobID = ttgC.JobID
AND ttg.PartCode = ttgC.PartCode
where (ttg.TagHeight != ttgC.TagHeight OR ttg.TagWidth != ttgC.TagWidth)
AND ((ttg.TagHeight >= ttgC.TagHeight AND ttg.TagWidth >= ttgC.TagWidth) 
    OR (ttg.TagHeight > ttgC.TagHeight AND ttg.TagWidth < ttgC.TagWidth))
order by ttg.PartCode

The difference is using > to compare them instead of !=

Tetraneutron 2009-05-28 23:40:30

Thanks, this works really well!

Noah 2009-05-28 23:45:00

Works well in that case, yes. However, in other cases, it won't - such as if the heights aren't equal. So the quick solution is to make that a ">" also. But then, if height is greater on one row, but width is lesser - you'll be right back where you started. See my answer for a solution.

TheSoftwareJedi 2009-05-28 23:54:49

As far as I understand the problem it will still work,an example (In SQL server syntax)Both Keyvals 4 and 5 are returned which have both valA and valB different

Tetraneutron 2009-05-29 00:28:14

create table Test(PK int identity(1,1), KeyVal int, valA int, valB int) insert into Test values (1, 1, 1) insert into Test values (1, 1, 1) insert into Test values (2, 1, 1) insert into Test values (2, 2, 1) insert into Test values (3, 1, 2) insert into Test values (3, 1, 1) insert into Test values (4, 1, 2) insert into Test values (4, 2, 1) insert into Test values (5, 2, 1) insert into Test values (5, 1, 2) select * from Test A join Test B on A.valA <> B.valA and A.valB > B.valB and A.KeyVal = B.KeyVal

Tetraneutron 2009-05-29 00:28:19

@Tetraneutron - he's looking for the part which has width OR height different. your SQL has an AND in the join. Change that to an OR.

TheSoftwareJedi 2009-05-29 00:54:03

I think its right, The check to see if they are different has an OR in it, then to restrict it to one row per difference it checks that the first table is >= the second for both columns (which by the previous check at least one column will be) - Of course as stated below if you have some key field (say a candidate key (unique) that is comparable with > you could use that (as the only greater than comparison (in which case you wouldn't use >=))

Tetraneutron 2009-05-29 01:46:59

Answer 2

A:

The question becomes, which information do you want from the rows. They are different rows.

If you just want the part code, this will work:

select ttg.PartCode
from tblTagGlass ttg
inner join tblTagGlass ttgC ON
ttg.JobID = ttgC.JobID
AND ttg.PartCode = ttgC.PartCode
where ttg.TagHeight != ttgC.TagHeight
or ttg.TagWidth != ttgC.TagWidth
group by ttg.PartCode
order by ttg.PartCode

Aside from that, you'll need to decide what aggregation of information you want across the rows.

TheSoftwareJedi 2009-05-28 23:41:29

I want the UnitCode, PartCode, Height, and Width the mismatches

Noah 2009-05-28 23:46:37

if they are mismatches, which Height/Width do you want? Your logic is a bit flawed...

TheSoftwareJedi 2009-05-28 23:55:37

love getting down voted for providing the correct answer. God bless SO. lol

TheSoftwareJedi 2009-05-29 00:54:53

Answer 3

+1 A:

Let's make an assumption:

PRIMARY KEY(JobCode, UnitCode, PartCode)

What you are looking for is items with the same JobCode and PartCode but with different UnitCode values - and with a difference in either the TagHeight or TagWidth (or both). So, use the '>' trick on UnitCode to distinguish between rows and prevent duplicates, but '!=' to detect the differences in TagHeight or TagWidth:

SELECT *
    FROM tblTagGlass ttg JOIN tblTagGlass ttgC
         ON  ttg.JobID = ttgC.JobID
         AND ttg.PartCode = ttgC.PartCode
         AND ttg.UnitCode > ttgC.UnitCode
    WHERE (ttg.TagHeight != ttgC.TagHeight
       OR ttg.TagWidth != ttgC.TagWidth)
    ORDER BY ttg.PartCode

Jonathan Leffler 2009-05-29 01:03:53

ansaurus

tags:

views:

answers:

Remove double join results from query

related questions