Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Distinct on a right/left inner join

Posted on 2003-11-20
7
Medium Priority
?
7,985 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 1000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
Suggested Courses

618 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