Distinct values and join query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Distinct values and join query

Author

Topic

sengreen
Starting Member

3 Posts

Posted - 2015-02-24 : 14:39:42

[code]
SELECT first.name,first.country, second.name, second.string
FROM
first LEFT OUTER JOIN second
ON
first.name = second.name
WHERE
first.date='2015/02/24'
[/code]
This query means all record from second table and matching record from first table. Now my question is that on 24 Feb 2015 I have duplicate names in second table and I want distinct names from second table and then its matching values from first table. Now my query is showing all duplicate values from second table and its matching record from first table. Any help in this regard. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-24 : 15:03:39

I'm confused by your description. In your example query, it is grabbing matching rows between first and second and then also any unmatching rows from first. Your description has it the other way around, and I want to be sure I understand your problem.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-24 : 15:05:48

Also, which second.string do you want to show? Or are they dupes too?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sengreen
Starting Member

3 Posts

Posted - 2015-02-24 : 20:47:59

Okay. The problem is that basically the sources of both tables are different or not user generated may be machine generated. We cant say these are normalized because I don't have primary key in first table and If I have then it would not be foreign key in other table. What I have is name column. Name column of first table is unique we can say, on the other hand name column of second table has duplicate values and I am matching names of second table with names of first table and getting all records from second and matching from first but I want only distinct records from second table. Or I can break my problem in two peaces. First getting distinct records from second table and against only these records I want to get records from first table but then I need to have id as primary key. I am confused due to this kind of data.

sengreen
Starting Member

3 Posts

Posted - 2015-02-24 : 22:57:41

Sample Data
first Table(Data Already available)
Number name country
12345 A Canada
23456 B USA
34567 C Australia
45678 D Japan
56789 E India

Second Table(Data coming from automated source). As data comes in this table, I am inserting a unique id with every row programmatically.
Id(PK) Number
1 34567
2 45678
3 56789
4 98989
5 76767
6 34567
7 45678

In second table only first three, 6th and 7th records Numbers are available in first table. I want to show distict data from second table including name and country from first Table where such information is available and other rows will come blank. Duplicate values will not be shown(6,7). This is scenario where I am totally stuck.

Result required
Number name country
34567 C Australia
45678 D Japan
56789 E India
98989
76767

Thanks for your kind consideration

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-02-25 : 11:00:39

[code]
-- *** Test Data ***
CREATE TABLE #first
(
Number int NOT NULL
,name varchar(20) NOT NULL
,country varchar(20) NOT NULL
)
INSERT INTO #first
SELECT 12345, 'A', 'Canada'
UNION ALL SELECT 23456, 'B', 'USA'
UNION ALL SELECT 34567, 'C', 'Australia'
UNION ALL SELECT 45678, 'D', 'Japan'
UNION ALL SELECT 56789, 'E', 'India';

CREATE TABLE #second
(
Id int NOT NULL
,Number int NOT NULL
)
INSERT INTO #second
SELECT 1, 34567
UNION ALL SELECT 2, 45678
UNION ALL SELECT 3, 56789
UNION ALL SELECT 4, 98989
UNION ALL SELECT 5, 76767
UNION ALL SELECT 6, 34567
UNION ALL SELECT 7, 45678;
-- *** End Test Data ***

SELECT DISTINCT S.Number, F.name, F.country
FROM #second S
LEFT JOIN #first F
ON S.Number = F.Number;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources