• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Select Returns Duplicates lines

Hi

I have in issue where a select statement is duplicating lines.  This is due to the following

Table1
Col1_1            Col1_2
1                           Bob
2                           Bill
3                           Ted

Table2
Col2_1            Col2_2
1            Red
1            Blue
2            Red
3            Green

If I do a

select Col1_1, Col1_2, Col2_2
From    Table_1 INNER JOIN Table_2 ON Table_1.Col1_1 = Table_2.Col2_1

I get 2 lines for Bob.

How can I get just one line?

Cheers

Brasso  
0
brasso_42
Asked:
brasso_42
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, depends on what information from Table2.Col2_2 you want to get, in the end result.
please clarify
0
 
Patrick MatthewsCommented:
brasso_42 said:
>>How can I get just one line?

The answer depends on which record from Table2 "wins" in the join.  For example, you could do this:

select t1.Col1_1, t1.Col1_2, MAX(t2.Col2_2) AS Col2_2
From    Table_1 t1 INNER JOIN Table_2 t2 ON t1.Col1_1 = t2.Col2_1
GROUP BY t1.Col1_1, t1.Col1_2

or...

select t1.Col1_1, t1.Col1_2, MIN(t2.Col2_2) AS Col2_2
From    Table_1 t1 INNER JOIN Table_2 t2 ON t1.Col1_1 = t2.Col2_1
GROUP BY t1.Col1_1, t1.Col1_2
0
 
brasso_42Author Commented:
Well at the mo I'be happy with any thing :)

but I could could join the results e.g. Red Blue   that would be by far the best.  if not a min/max approach would be fine

Cheers

Brasso
0
 
brasso_42Author Commented:
Just spoken to my boss and what he really wants is them both on 1 line eg Red Blue

Sorry to be a pain

Many thanks for your help so far

Brasso
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now