Solved

Distinct on a right/left inner join

Posted on 2003-11-20
7
7,981 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 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