Solved

Distinct on a right/left inner join

Posted on 2003-11-20
7
7,982 Views
Last Modified: 2008-10-30
Hi,

I've got the need to select everything from table1 and only 1 row from table2. Table2 can hold any number of rows from 0 to 10,000

I've tried the following with no joy. Any ideas?

SELECT DISTINCT (ID) AS Expr1, *
FROM aacc LEFT JOIN awardsQuery ON aacc.ID = tbl2.feildID;

This still brings back IDs that are the same!

Regards
0
Comment
Question by:devaid
[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
7 Comments
 
LVL 4

Expert Comment

by:1mak
ID: 9787010
The DISTINCT keyword acts on all of the fields returned, not just the first one (i.e. ID in your example).

If you were to explicitly name only the fields you wanted then i'm sure you'd get a better result.
e.g.
      SELECT DISTINCT      aacc.ID,
                  awardsQuery.Field1
      FROM            aacc
          LEFT JOIN      awardsQuery ON aacc.ID = awardsQuery.FieldID


0
 
LVL 2

Author Comment

by:devaid
ID: 9788484
Still returns duplicate rows
0
 
LVL 35

Expert Comment

by:YZlat
ID: 9788705
try this:

strSQL="SELECT DISTINCT ID FROM aacc LEFT JOIN awardsQuery ON aacc.ID = tbl2.feildID;"

set db=CurrentFB
set rst=db.Execute(strSQL)

if not (rst.eof and rst.bof) then
      Do while not rst.eof
            strSQL="SELECT * FROM aacc LEFT JOIN awardsQuery ON aacc.ID = tbl2.feildID AND ID = " & rst("ID") & ";"
            set rst2=db.execute(strSQL)
            if not (rst2.eof and rst2.bof) then

            ...............

            'YOUR CODE HERE
            end if
             rst.MoveNext
      Loop
end if
      


      
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 35

Expert Comment

by:YZlat
ID: 9788721
sorry

strSQL="SELECT DISTINCT ID FROM aacc LEFT JOIN awardsQuery ON aacc.ID = tbl2.feildID;"

set db=CurrentFB
set rst=db.Execute(strSQL)

if not (rst.eof and rst.bof) then
     Do while not rst.eof
          strSQL="SELECT * FROM aacc LEFT JOIN awardsQuery ON aacc.ID = tbl2.feildID AND ID = " & rst("ID") & ";"
          set rst2=db.execute(strSQL)
          if not (rst2.eof and rst2.bof) then
      Do while not rst2.eof
                      'YOUR CODE HERE
            .............................


            rst2.Movenext
      Loop
          end if
           rst.MoveNext
     Loop
end if
     


0
 
LVL 18

Accepted Solution

by:
BigSchmuh earned 250 total points
ID: 9789488
There is a way to do this without any code scripting using a simple GROUP BY clause but you need to have a combination of columns in awardsQuery that are unique.
Below, I assume that you have an ID field in awardsQuery that is unique but you can do the same with a multi-columns unique key.

SELECT aacc.*, awardsQuery.*
FROM aacc
 LEFT JOIN (SELECT fieldID, Min(ID) As MinID
                  FROM awardsQuery
                  GROUP BY fieldID) LowestID ON LowestID.fieldID = aacc.ID
 LEFT JOIN awardsQuery on LowestID.ID = awardsQuery.ID

Please note that if aacc.ID is NOT unique you should add the DISTINCT keyword after the first SELECT

Hope this helps.
0
 
LVL 8

Expert Comment

by:gajender_99
ID: 9795918
hi try this

SELECT DISTINCT aacc.ID AS Expr1,tbl2. *
FROM aacc LEFT JOIN tbl2 ON aacc.ID = tbl2.feildID;

this should solve you problem
0
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 9795984
gajender_99:
==> You are welcomed to read others posts as "1mak" already post the same non-working solution.
0

Featured Post

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

718 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