Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Select Returns Duplicates lines

Posted on 2008-10-24
5
Medium Priority
?
203 Views
Last Modified: 2012-05-05
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
Comment
Question by:brasso_42
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22794933
well, depends on what information from Table2.Col2_2 you want to get, in the end result.
please clarify
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 22794936
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
 
LVL 1

Author Comment

by:brasso_42
ID: 22794944
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
 
LVL 1

Author Comment

by:brasso_42
ID: 22794990
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question