We help IT Professionals succeed at work.

JOIN, INNER JOIN, OUTER JOIN, RIGHT JOIN, LEFT JOIN

mcrmg
mcrmg asked
on
251,402 Views
Last Modified: 2011-08-18
Hi,

Can EE give me some examples?  Thanks
Comment
Watch Question

Alan WarrenApplications Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alan WarrenApplications Developer

Commented:

Author

Commented:
RIGHT JOIN, LEFT JOIN
I got this part.  Thanks




INNER JOIN, OUTER JOIN,

Are they the same as full join??
Alan WarrenApplications Developer

Commented:

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.

Alan

Author

Commented:
How about OUTTER??  Thx
Alan WarrenApplications Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alan WarrenApplications Developer

Commented:
Hi mcrmg,

You should find all you need to know about SQL Joins here:
This is the link you want, save me posting all the contents.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp

Alan
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Hi,

I have to work with MS Access 2000. This version doesn't support (full) outer join.
How can I substitute this with a combination of left/right/inner join. Performance isn't a problem (small tables). Also temporary tables are possible.

Hello,

This entire thread contain very valuable information.  I am adding a printout of it to my "Hall of Access Information" which includes info that may need later.

Can the joins be used for two table when one is not an Access table?  For example, if one is an Excel tables to be joined withe the Access table.  Both have the same field names in the same order.

WRS

Commented:
If you need to use a combination of LEFT and RIGHT JOINs to yield a FULL JOIN, here's what I did

Firstly use a UNION ALL between the RIGHT and LEFT, then encapsulate it with a DISTINCT * on the resulting table.  Suffice to say the only difference between the two queries in the UNION should be the LEFT or RIGHT JOIN operater

Here's an example on what I was working on

SELECT DISTINCT * FROM (

      SELECT       s.CountryName AS CountryNme,
            s.[Sampled Date] AS SampledReceiptedDate,
            s.WgtSampled,
            s.ItemsSampled,
            s.IPK,
            s.CountOfBag,
            r.sum_bags,
            r.sum_weight
      FROM qryMailSampledSep AS s
      RIGHT JOIN qryMailReceiptedSep AS r
      ON (s.[Sampled Date] = r.[receipt Date])
      AND (s.CountryName = r.Country)

      UNION ALL

      SELECT       s.CountryName AS CountryNme,
            s.[Sampled Date] AS SampledReceiptedDate,
            s.WgtSampled,
            s.ItemsSampled,
            s.IPK,
            s.CountOfBag,
            r.sum_bags,
            r.sum_weight
      FROM qryMailSampledSep AS s
      LEFT JOIN qryMailReceiptedSep AS r
      ON (s.[Sampled Date] = r.[receipt Date])
      AND (s.CountryName = r.Country);

) as tbl

ORDER BY SampledReceiptedDate, CountryNme

Lemme know how you go
October 17, 2004


Thanks  dcruzdf.  

Your information is very helpful to me and will probably be useful to others also.

WRSherman

Commented:
Question well addressed.
I have tried the above mentioned exercise to get an OUTER join to work in Access JET sql.  This expression gives the following error "Join expression not supported"


SELECT DISTINCT * FROM (
SELECT * FROM (gp LEFT JOIN lp ON gp.name = lp.name) LEFT JOIN temp ON gp.name = temp.name UNION ALL

 ( SELECT * FROM (gp RIGHT JOIN temp ON gp.name = temp.name) RIGHT JOIN lp ON gp.name = lp.name) );

Any ideas?  Thanks
Thanks for all the explanations
Luke ChungPresident
CERTIFIED EXPERT

Commented:
Here's a paper I wrote on Microsoft Access Query Tips and Techniques that you may find useful: http://www.fmsinc.com/tpapers/queries/index.html
There are several examples of multi-table joins including implied joins using criteria. It also has links to other papers on Update Queries, Non-Updateable Queries, Crosstab Queries, etc.
Hope this helps.
Luke ChungPresident
CERTIFIED EXPERT

Commented:
Here's a recent paper we published on the Not-In query and using LEFT JOIN and RIGHT JOIN to get them. Hope it's helpful:
http://www.fmsinc.com/MicrosoftAccess/query/outer-join/index.html 
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.