?
Solved

Distinct on a right/left inner join

Posted on 2003-11-20
7
Medium Priority
?
7,987 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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

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.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

839 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