|
To have the duplicates removed, I think a union would do that.
SELECT *
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
UNION
SELECT *
FROM TableA A
INNER JOIN TableB B
ON A.Field1<>B.Field1 AND A.Field2=B.Field2
;
Try that.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I think that's basically what Luc suggested -- and it doesn't work.
|
|
|
|
|
It's similar to what other's have suggested except that it's a union of the two result sets, which should eliminate any duplicates.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Chris Meech wrote: should eliminate any duplicates.
Except it doesn't.
I have added some clarification and sample data to my post.
|
|
|
|
|
It will remove the duplicates of the resultant set, but now that I've read your example, that is not quite what you are after. In your example once the row with ID 12 from table A matched on FieldA for the row with ID 20, you don't want to include it anymore where it might match on FieldB.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
PIEBALDconsult wrote: so I want 20
Do you want 20 in both rows with the Id from Table A being 10, and 12?
""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
|
Your modified message is much clearer. And this is what works for me:
SELECT * FROM TableA A INNER JOIN TableB B ON A.Field1=B.Field1
UNION ALL
SELECT * FROM TableA A INNER JOIN TableB B ON A.Field2=B.Field2
WHERE NOT A.ID IN (SELECT A.ID FROM TableA A INNER JOIN TableB B ON A.Field1=B.Field1)
just 3 SELECTs, no LEFT
PS: I failed to get it to work with a CTE on SQL Server...
modified 7-Jun-12 11:24am.
|
|
|
|
|
That's actually my solution posted above - UNION vs. UNION ALL does not make a difference here, because both queries select all columns from the same table.
|
|
|
|
|
Sorry, I missed your post, yes it is the same. Good work!
|
|
|
|
|
Here's another way to do it:
WITH CTE AS
(
SELECT A.ID aID
,A.Field1 aField1
,A.Field2 aField2
,CASE WHEN L.ID IS NULL THEN R.ID ELSE L.ID end bID
,CASE WHEN L.Field1 IS NULL THEN R.field1 ELSE L.Field1 END bField1
,CASE WHEN L.Field2 IS NULL THEN R.Field2 ELSE L.Field2 END bField2
FROM TableB L
right OUTER JOIN TableA A
ON l.field1 = a.field1
left OUTER JOIN TableB R
ON a.field2 = r.field2
)
SELECT aID
,aField1
,aField2
,bID
,bField1
,bField2
FROM CTE
WHERE bid IS NOT null The plan indicates that it should be faster, but that's with dummy data.
I'm curious about the performance with real data.
|
|
|
|
|
Interesting.
IMO it can be simplified further, as the CTE isn't really necessary, so I now have:
SELECT A.ID aID
,A.Field1 aField1
,A.Field2 aField2
,CASE WHEN L.ID IS NULL THEN R.ID ELSE L.ID end bID
,CASE WHEN L.ID IS NULL THEN R.field1 ELSE L.Field1 END bField1
,CASE WHEN L.ID IS NULL THEN R.Field2 ELSE L.Field2 END bField2
FROM TableB L
RIGHT OUTER JOIN TableA A ON L.Field1 = A.Field1
LEFT OUTER JOIN TableB R ON A.Field2 = R.Field2
WHERE L.ID IS NOT NULL OR R.ID IS NOT NULL
I have your cases depend on the ID field, not the other fields (where null might be valid)
|
|
|
|
|
I'm getting identical plans, but your version is prettier.
|
|
|
|
|
I have a sql table with following schema.
DateStamp
BatchID
MixName
MixComment
BatchDestination
BatchComment
MixNo
MixRev
Ing1Name
Ing1Size
Ing1PRM
Ing1AcceptedUnderTol
Ing1SPCSample
Ing1TargetWgt
Ing1ActualWgt
Ing1PartialActualWgt
Ing1PreactWgt
Ing1SlowFeedTime
Ing1FastFeedTime
Ing2Name
Ing2Size
Ing2PRM
Ing2AcceptedUnderTol
Ing2SPCSample
Ing2TargetWgt
Ing2ActualWgt
Ing2PartialActualWgt
Ing2PreactWgt
Ing2SlowFeedTime
Ing2FastFeedTime
Ing3Name
Ing3Size
Ing3PRM
Ing3AcceptedUnderTol
Ing3SPCSample
Ing3TargetWgt
Ing3ActualWgt
Ing3PartialActualWgt
Ing3PreactWgt
Ing3SlowFeedTime
Ing3FastFeedTime
Ing4Name
Ing4Size
Ing4PRM
Ing4AcceptedUnderTol
Ing4SPCSample
Ing4TargetWgt
Ing4ActualWgt
Ing4PartialActualWgt
Ing4PreactWgt
Ing4SlowFeedTime
Ing4FastFeedTime
Ing5Name
Ing5Size
Ing5PRM
Ing5AcceptedUnderTol
Ing5SPCSample
Ing5TargetWgt
Ing5ActualWgt
Ing5PartialActualWgt
Ing5PreactWgt
Ing5SlowFeedTime
Ing5FastFeedTime
Ing6Name
Ing6Size
Ing6PRM
Ing6AcceptedUnderTol
Ing6SPCSample
Ing6TargetWgt
Ing6ActualWgt
Ing6PartialActualWgt
Ing6PreactWgt
Ing6SlowFeedTime
Ing6FastFeedTime
Ing7Name
Ing7Size
Ing7PRM
Ing7AcceptedUnderTol
Ing7SPCSample
Ing7TargetWgt
Ing7ActualWgt
Ing7PartialActualWgt
Ing7PreactWgt
Ing7SlowFeedTime
Ing7FastFeedTime
Ing8Name
Ing8Size
Ing8PRM
Ing8AcceptedUnderTol
Ing8SPCSample
Ing8TargetWgt
Ing8ActualWgt
Ing8PartialActualWgt
Ing8PreactWgt
Ing8SlowFeedTime
Ing8FastFeedTime
Ing9Name
Ing9Size
Ing9PRM
Ing9AcceptedUnderTol
Ing9SPCSample
Ing9TargetWgt
Ing9ActualWgt
Ing9PartialActualWgt
Ing9PreactWgt
Ing9SlowFeedTime
Ing9FastFeedTime
Ing10Name
Ing10Size
Ing10PRM
Ing10AcceptedUnderTol
Ing10SPCSample
Ing10TargetWgt
Ing10ActualWgt
Ing10PartialActualWgt
Ing10PreactWgt
Ing10SlowFeedTime
Ing10FastFeedTime
Ing11Name
Ing11Size
Ing11PRM
Ing11AcceptedUnderTol
Ing11SPCSample
Ing11TargetWgt
Ing11ActualWgt
Ing11PartialActualWgt
Ing11PreactWgt
Ing11SlowFeedTime
Ing11FastFeedTime
Ing12Name
Ing12Size
Ing12PRM
Ing12AcceptedUnderTol
Ing12SPCSample
Ing12TargetWgt
Ing12ActualWgt
Ing12PartialActualWgt
Ing12PreactWgt
Ing12SlowFeedTime
Ing12FastFeedTime
TimeStarted
TimeComp
ManIntervention
HA_Ing1Name
HA_Ing1Size
HA_Ing1PRM
HA_Ing1TargetWgt
HA_Ing1ActualWgt
HA_Ing2Name
HA_Ing2Size
HA_Ing2PRM
HA_Ing2TargetWgt
HA_Ing2ActualWgt
HA_Ing3Name
HA_Ing3Size
HA_Ing3PRM
HA_Ing3TargetWgt
HA_Ing3ActualWgt
HA_Ing4Name
HA_Ing4Size
HA_Ing4PRM
HA_Ing4TargetWgt
HA_Ing4ActualWgt
HA_Ing5Name
HA_Ing5Size
HA_Ing5PRM
HA_Ing5TargetWgt
HA_Ing5ActualWgt
HA_Ing6Name
HA_Ing6Size
HA_Ing6PRM
HA_Ing6TargetWgt
HA_Ing6ActualWgt
HA_Ing7Name
HA_Ing7Size
HA_Ing7PRM
HA_Ing7TargetWgt
HA_Ing7ActualWgt
HA_Ing8Name
HA_Ing8Size
HA_Ing8PRM
HA_Ing8TargetWgt
HA_Ing8ActualWgt
HA_Ing9Name
HA_Ing9Size
HA_Ing9PRM
HA_Ing9TargetWgt
HA_Ing9ActualWgt
HA_Ing10Name
HA_Ing10Size
HA_Ing10PRM
HA_Ing10TargetWgt
HA_Ing10ActualWgt
HA_Ing11Name
HA_Ing11Size
HA_Ing11PRM
HA_Ing11TargetWgt
HA_Ing11ActualWgt
HA_Ing12Name
HA_Ing12Size
HA_Ing12PRM
HA_Ing12TargetWgt
HA_Ing12ActualWgt
HA_Ing13Name
HA_Ing13Size
HA_Ing13PRM
HA_Ing13TargetWgt
HA_Ing13ActualWgt
HA_Ing14Name
HA_Ing14Size
HA_Ing14PRM
HA_Ing14TargetWgt
HA_Ing14ActualWgt
HA_Ing15Name
HA_Ing15Size
HA_Ing15PRM
HA_Ing15TargetWgt
HA_Ing15ActualWgt
HA_Ing16Name
HA_Ing16Size
HA_Ing16PRM
HA_Ing16TargetWgt
HA_Ing16ActualWgt
HA_TimeStarted
HA_TimeComp
I have been asked to sort of transpose this one long record, into another table that has multiple records associated with one batchid. So essentially instead of 11 repeating sets of columns for ingredient information they want multiple records for one batch id in which there is a seperate record for each ingredient and the data is stored under one set of columns from the 11 duplicate ingredient columns sets above.
So they want...
DateStamp
BatchID
MixName
MixComment
BatchDestination
BatchComment
MixNo
MixRev
Bin Number (Instead of a set of columns for each bin)
IngName
IngSize
IngPRM
and this would repeat for each ingredient within that batch.
My question is how can i transpose the above table which already exists into the lower table? Would I use a trigger on the top table? And insert into another table?
|
|
|
|
|
You may want to create a view which satisfies the requirement rather than a whole new table.
Just a thought.
Good luck
|
|
|
|
|
How would I do what I described with a view? I am still learning with SQL server?
|
|
|
|
|
The keyword you're looking for is UNPIVOT .
What database are you using?
|
|
|
|
|
What a classic, this is the most basic of error in database design and now you have to deal with the issue.
Is the change in structure going to be a permanent change to the data structure or is it just to facilitate some reporting requirement?
I would go with the view solution if it is for reporting. A series (16 of them) of UNION queries would do the job but t would be ugly.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This will be used only for reporting purposes.
|
|
|
|
|
Then go with the view idea
SELECT commonfields,
Ing1Name AS fieldanme,
Ing1Size,
Ing1PRM,
Ing1AcceptedUnderTol,
Ing1SPCSample,
Ing1TargetWgt,
Ing1ActualWgt,
Ing1PartialActualWgt,
Ing1PreactWgt,
Ing1SlowFeedTime,
Ing1FastFeedTime
FROM SomTable
UNION ALL
SELECT commonfields,
Ing2Name,
Ing2Size,
Ing2PRM,
Ing2AcceptedUnderTol,
Ing2SPCSample,
Ing2TargetWgt,
Ing2ActualWgt,
Ing2PartialActualWgt,
Ing2PreactWgt,
Ing2SlowFeedTime,
Ing2FastFeedTime
FROM SomTable
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I want to build a small bank application using sql oracle server
as back end and c# as the front end for my practice.
|
|
|
|
|
Okay. Put together the requirements, and start planning it out. This is how you start any project. People are not going to just drop what they are doing and give you code to any kind of application.
""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
What sort of banking application? HR? Trading? M&A? Retail?
Why don't you start by reading some books on programming before you decide you want to create something as complex as a 'banking' application?
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
I'm sorry I only do big banks. Can't help you out.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Chris Meech wrote: I'm sorry I only rob banks. Can't help you out. FTFY
thatrajaNobody remains a virgin, Life screws everyone
|
|
|
|