September 7, 2018 at 5:38 am
Hi Guys,
I'm hoping for some help with these joins. From my sample data you will see I am get duplicates in the last query which joins the two table together. Is there a way to avoid this and essentially remove the duplicates from the NOTE column or is this happening because of the SEQ number? Not even sure if this is possible.
Many thanks
-- Sample data
IF OBJECT_ID('tempdb..#lab') IS NOT NULL DROP TABLE #lab
SELECT * INTO #lab FROM (VALUES
( 'MCH', '64131', '1', 'BIO KLENZ ANTI BACTERIAL BIO FUEL TREATMENT', '124' ),
( 'MCH', '64131', '2', 'CARRY OUT 1ST FIXED SERVICE', '124'),
( 'MCH', '64131', '3', 'CARRY OUT AIR CON DE-BUG AND RE-GAS', '124' ),
( 'MCH', '64131', '4', 'CARRY OUT BRAKE FLUID CHANGE', '124' ),
( 'MCH', '64131', '5', 'Carry out Vehicle Condition Video', '124' ),
( 'MCH', '64131', '6', 'REPLACE AUTOMATIC TRANSMISSION GEAR BOX OIL', '124' ),
( 'MCH', '64131', '7', 'REPLACE CAMBELT', '124' ),
( 'MCH', '64131', '8', 'SPECIAL LINE - PLEASE DO NOT AMEND!T', '124' )) d
( NAME, REF, LINE, NOTE, REF2)
IF OBJECT_ID('tempdb..#Log') IS NOT NULL DROP TABLE #Log
SELECT * INTO #Log FROM (VALUES
( '10', 'MCH', '64131', '07/09/2018 09:56', 'BACDEBUGGAS', 'OBB'),
( '11', 'MCH', '64131', '07/09/2018 10:00', 'SER', 'ENQ'),
( '12', 'MCH', '64131', '07/09/2018 10:03', 'SER', 'ENQ' )) d
( SEQ, NAME, REF, DATE, CODE, TRAN1)
SELECT * FROM #Log
SELECT * FROM #lab
SELECT * FROM #Log L
JOIN #lab LA ON LA.REF=L.REF
WHERE L.REF = '64131'
AND l.TRAN1 IN ('IBB', 'OBB', 'W3B', 'ENQ')
September 7, 2018 at 5:58 am
If the only link between the two tables is the REF column, you have a problem, because that is a many-to-many relationship.
Are you able to show us which results you would like to see? There may be creative ways of getting to it.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 7, 2018 at 6:01 am
I cannot see any duplicates there.
Your join criteria match each of 8 rows in #Lab to each of 3 rows in #Log.
Total must be 24 rows.
That's exactly what the query returns.
No duplicates.
Now - what are you trying to achieve?
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply