Retrieving distinct rows from two related tables. T-SQL

Question

0.00/5 (No votes)

See more:

Have two related tables in a one to many relationship.
I have a select statement like

Select MasterTable.*, DetailTable.Field1
From MasterTable INNER JOIN DetailTable
WHERE Criteria

It Returns several rows for each of the master key field of the master table, even if it does not involve the Field1 field in the criteria.

I would like to filter the results such that only one row is returned per MasterKey field on the master table.

Thanks in advance

What I have tried:

I have tried SELECT DISTINCT
And also GROUP BY
but I do not seem to hit it Right.

Posted 18-Dec-18 4:45am

Juan C Henao

Updated 18-Dec-18 7:01am

RedDk

v2

Add a Solution

Comments

Wendelius 18-Dec-18 11:38am

If you want only 1 row for each row in master table, how the different values in DetailTable.Field1 should be handled?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dirk Bahle · Answer 1 · 2018-12-18T05:10:00

It would be best if you could define the 2 tables in terms of their rows and join criteria - a create table statement for each would be best - because otherwise its difficult to help you since you do not detail what you tried and what the error message was either :-(

You are right, select distinct can be used to retrieve distince (unique) valued rows but you have to be careful with those rows that occur in both tables. Be sure to always specifiy the table you are revering to:

SQL

Select distinc m.Field1, d.Field2
From MasterTable m
    INNER JOIN DetailTable d
   on m.key = d.key
WHERE Criteria

How about a statment like the above where selected field is specified with a specific table m or d for Master and DetailTable. Does this work? If not what is the specific table design, query statement and error message?

As for the group by this is also useful for selecting distinct rows but it is usually only used when you want to count things, like dublicate rows or determine min() or max() values. A typical usage is like the statement above but with a group by clause and you have to use extra care to select only values that are also present in the group by clause (min() or max(), or sum()) can be selected without having to be in the group by clause per se). So how about a statment like this:

SQL

Select m.Field1, d.Field2, count(*)
From MasterTable m
    INNER JOIN DetailTable d
   on m.key = d.key
WHERE Criteria
 group by m.Field1, d.Field2

Does this work? If not what is the specific table design, query statement and error message?