Outer Join and a two step query

Hi,
I need to list which dog breeds have never been sold at the Store by using 
Outer Join and a two step query. Anyone can explain for me Out join 

Thank you all

My tables are as follows:
Table Animal has
AnimalID
Category
Breed

Table Customers
CustomerID
Phone
Name

Table Sales
CustomeriD
SaleDate
SaleID

Table Saleanimal
SaleID
AnimalID
SalePrice

Table SaleItem
SaleiD
ItemID

0
Utf
11/26/2009 9:03:01 AM
access 16762 articles. 3 followers. Follow

2 Replies
913 Views

Similar Articles

[PageSpeed] 32

The following SQL should list which dogs have not been sold at the store:

SELECT Animal.AnimalID, Animal.Category, Animal.Breed
FROM Animal LEFT JOIN Saleanimal
ON Animal.AnimalID = Saleanimal.AnimalID
WHERE Saleanimal.AnimalID IS NULL

Another, less efficient, approach would be

SELECT AnimalID, Category, Breed
FROM Animal
WHERE AnimalID NOT IN
(SELECT DISTINCT AnimalID
FROM Saleanimal)

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jon" <Jon@discussions.microsoft.com> wrote in message 
news:1A060F9B-B547-4A91-B397-4874BAED4AD8@microsoft.com...
> Hi,
> I need to list which dog breeds have never been sold at the Store by using
> Outer Join and a two step query. Anyone can explain for me Out join
>
> Thank you all
>
> My tables are as follows:
> Table Animal has
> AnimalID
> Category
> Breed
>
> Table Customers
> CustomerID
> Phone
> Name
>
> Table Sales
> CustomeriD
> SaleDate
> SaleID
>
> Table Saleanimal
> SaleID
> AnimalID
> SalePrice
>
> Table SaleItem
> SaleiD
> ItemID
> 


0
Douglas
11/26/2009 12:29:02 PM
If using a subquery, a more efficient approach would be:

SELECT *
FROM Animal 
WHERE NOT EXISTS
    (SELECT *
      FROM Saleanimal
      WHERE Saleanimal.AnimalID = Animal.AnimalID);

Its worth noting that using a NOT IN operation against a subquery would work
here, albeit less efficiently,  as the Saleanimal table cannot contain a NULL
AnimalID.  For future reference, however, be aware that a NOT IN operation
should not be used in cases where the set of values returned by the subquery
could contain a NULL.  This is because x NOT IN(a,b,c) equates to x <> a AND
x <> b AND x <> c.  If a and c are values but b is NULL say, then x <> b =
NULL, so the if x = neither a nor c, the expression becomes TRUE AND NULL AND
TRUE, so the whole expression evaluates to NULL, neither TRUE nor FALSE.
Using the NOT EXISTS predicate overcomes this, as well as being generally
more efficient.

On the other hand x IN(a,b,c) will work even if b is NULL as it equates to x
= a OR x = b OR x = c, so only one has to be TRUE rather than all three as
with the AND operation above.

A LEFT OUTER JOIN, testing for a NULL Saleanimal.AnimalID as suggested by
Doug is almost certainly the most efficient, however.

Ken Sheridan
Stafford, England

Jon wrote:
>Hi,
>I need to list which dog breeds have never been sold at the Store by using 
>Outer Join and a two step query. Anyone can explain for me Out join 
>
>Thank you all
>
>My tables are as follows:
>Table Animal has
>AnimalID
>Category
>Breed
>
>Table Customers
>CustomerID
>Phone
>Name
>
>Table Sales
>CustomeriD
>SaleDate
>SaleID
>
>Table Saleanimal
>SaleID
>AnimalID
>SalePrice
>
>Table SaleItem
>SaleiD
>ItemID

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200911/1

0
KenSheridan
11/26/2009 1:35:33 PM
Reply:

Similar Artilces:

How do I view and edit two powerpoint documents at the same time?
Hello, i have just updated to office 2007. I am working on apresentation, and want to view two powerpoint documents at the same time. Eg, my version and the group version at the same time. Whenever i click on a document in the start bar, it just replaces the other document, I cannot view the two powerpoint documents side by side. I am sure this is just a setting I have not changed on my powerpoint, as in Word I can view two documents at the same time fine. Thank you for any help. In article <77EE398A-7971-46F7-9A6B-850CCF7105ED@microsoft.com>, Kaod55 wrote: > Hello, ...

Can't outer join or append.
I am getting daily data from units that may or may not be reporting each day. If I use an inner join, then the units that do not report on any given day, are dropped from the query. I tried to do a multiple join but evidently Access will not permit more than two tables to be joined using an outer join. So I tried entering the data using the first day of data to establish the original query, and subsequent days to append to it. I got an error message stating that my original query was not updateable. What now? "Steven" <Steven@discussions.microsoft.com> wr...

Percentage between two numbers
Hi, i have this two situations: 2394 34 -98,58% 34 2394 6941,18% This is the formulas i used: =(B1-A1)/ABS(A1) =(B2-A2)/ABS(A2) I need a formula to calculate the percentage of differences between two numbers even if the first is bigger than the second or the other way around. This is how i need the final result to look: 2394 34 -98,58% 34 2394 +98,58% Can this be done? Thanks! Percentages don't work that way. If your investment portfolio drops 50% (from $10,000 to $5,000), you need a 100% increase to get back to your starting position. Similar...

Querying Data from Access
I am trying to pull data from Access into Excel. I have successfully done so... well, to a point. Here is my situation: I have a database containing several tables. I only want to pull from two of these tables. One contains inventory data and the other contains descriptions of the data I want to pull. For instance, in the inventory table I have a shirt. The shirt comes in several sizes, however the sizes are contained in a seperate table containing the descriptions and how many of each for the description. The two tables are tied together by unique ID's. What I would like to do ...

can outlook work without joining local domain?
we have situation where client wants to use exchange server 2003 without having workstations to join domain. Can this work? Thank you in advance. arun Yes, it works. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- "arun mx" <arunmx@discussions.microsoft.com> wrote in message news:7EB88EC5-3ACF-45CF-B14C-B75E44604872@microsoft.com... > we have situation where client wants to use exchange server 2003 without > having workstations to join domain. Can this work? > Thank you...

Dropdown Query
Dear all, I have two columns. Each column contains a defined value and each > value carries some predefined details. I am trying to place a dropdown > through data validation, so that i select any value, i should be getting all > the details related to that value without any extended space. Like below-: > > > A operations > A downtime > a client figure > B ratings > B flow chart > B technical support > > If i select A, I want to get the details of A as dropdown, i select B, i > want to get the details of B. > I have also visi...

VISIT THE GROUPS ___AND JOIN FOR BUSINESS BENIFIT...Bangladesh Business.......BizBangladesh.......Biz Bangladesh #2
VISIT THE GROUPS ___AND JOIN FOR BUSINESS BENIFIT...Bangladesh Business.......BizBangladesh.......Biz Bangladesh ...

Compare dates in two different ranges
Hi. I have the following data in a worksheet: A B DATE NUMBER 05/15/2010 321 05/16/2010 558 05/17/2010 665 05/18/2010 262 C D DATE NUMBER 06/10/2010 321 06/11/2010 558 06/12/2010 665 06/13/2010 262 This is data that I import from a company application, and as you can see, a number can have more than one date associated. I need to obtain in another range, the number (only once) and the dates associated to it. How can I do this comparat...

comparing numerically two documents
Hi all, I need to compare the text of document A with the text of document B with some Word functionality, plugin or tool that tells me how much the text in the two docs is different. Some kind of reporting feature that tells you something like "The text of document B is X % (percent) different from the text of document A" Is anythig like this available? thanks ...

Query problem
Well here I go again. Biting off more than my brain can handle. I have a report that shows all of our machines in our plant. I’m tracking both machine down time (the time the machine is broken) and Plant Down Time (the time the machine is broken that hampers production) I have a form that we use to gather all pertinent information such as the date, time notified of malfunction etc… I have a query that well, queries the data so I can view what is happening each month. That works great. Now to the meat and potatoes… I calculate down times by using: Minutes: DateDiff("n", [Time ...

Query Money 2006 database with Access
Is it possible to directly query the money 2006 database with Access? I would like to be able to create the reports that I need in Access and query the Money database. Thanks, Sam Sadly, no. There is MoneyLink, an Excel AddIn, but it is pretty weak and can't compare to direct access to the Money DB. See also http://umpmfaq.info/faqdb.php?q=10. "Sam" <neverchecked40@hotmail.com> wrote in message news:1128173491.984052.23870@o13g2000cwo.googlegroups.com... > Is it possible to directly query the money 2006 database with Access? > I would like to be able to c...

two-variable calculations continued ...
Could you be more specific, as I am still learning to program in excel. "Gary''s Student" wrote: > Use a worksheet change event macro. It can detect two cells entered and > calculate the third, See: > > http://www.mvps.org/dmcritchie/excel/event.htm#wb_shtChange > > -- > Gary''s Student > > > "fredonic" wrote: > > > If I input a formula e.g. ‘A1+B1=C1’; how do I tell excel to calculate B1 if > > I have A1 and C1’s values or A1 if I have the other 2 values – doing all this > > using only the 3 or...

Can't go to 'Last Record' in one step in a Word doc. linked To Exc
I have a excel spreadsheet linked to a word document. I can go to the last record one at a time but cannot use the 'last record' feature as it returns me to the first record. The 'first record' feature works. Also the record I was on in a previous use is still there when I return. TIA ...

lost two months of received emails
Help I lost about 2 months of received emails. They are not in my deleted folder. I already tried the pst restore utility. Thanks ...

Advanced Find
I want to do an Outer Join on Opportunities which does not have any related records in Opportunity Product. I can easily get all Opportunities that have related records in Opportunity product with data in the field, but how do I exclude records that do not have Opportunity Product ? Thanks for your help The key is the where clause... SELECT * FROM Opportunity oppy LEFT JOIN OpportunityProduct op ON oppy.opportunityid = op.opportunityid WHERE op.opportunityid IS NULL Ah, a simple SQL question... how refreshing! Dave Eric wrote: > I want to do an Outer Join on Opportunities...

Sync two Pocket Outlook
How to sync two Pocket Outlook into MS Outlook so my Pocket PC contacts, Tasks, calender,etc do not mix up with my wife Pocket Outlook? Hi - don't know, and I'm not sure you can do this, but if you post in microsoft.public.pocketpc you may get more help. Phang wrote: > How to sync two Pocket Outlook into MS Outlook so my > Pocket PC contacts, Tasks, calender,etc do not mix up with > my wife Pocket Outlook? ...

Public Folder Replication Reminder
Hi, I am posting this in the hope that I can confirm how public folders are replicated between two Exchange 2000 servers, which are installed in the same Exchange 2000 organisation, administrative group and routing group. Up to this point, we have had only a single Exchange server, but I have now been forced to install a second Exchange server within the same organsation, administrative and routing group as the single server is on it's last legs. I have only the default Public Folder Tree in operation and both my servers have Public Folder stores configured and mounted. I need to ...

Query Using Time
My table name is Master. I would like to create a query that uses the field [Login] from the Master table. I would like for the query to display any entry that comes after 8:05:59 AM. I have tried using >#8:05:59 AM#, but this does not work. Please offer any suggestions. Thanks. Nevermind...fixed the issue. Thanks. "Chris" wrote: > My table name is Master. I would like to create a query that uses the field > [Login] from the Master table. I would like for the query to display any > entry that comes after 8:05:59 AM. I have tried using >#8...

identify join sides of a table
Hi, I am looping through all the tables in a db and appending data to each table. The problem is that I can't append data to a table on the "many" side before i append the necessary data to the "one side" table. Is there a way to identify the join types (or "sides) that a table is involved in? That way i could loop through and append the tables that are on the "one" side first. Then do the tables that are on the "many side". I suppose this could get tricky because the table could be on the "many" side of one re...

Windows Server 2008 R2 DC
Hello, I am researching how to properly join a 2008 R2 domain in an automated fashion from a re-imaged XP SP3 computer; either through Sysprep, or through a script after sysprep has finished and mini-setup is concluded; I have a utility that renames my XP SP3 machines after sysprep is finished, so I am guessing I will need to have the machine join the domain after that happens. Any thoughts? Anyone else out there who manages Windows XP workstations in a Server 2008 R2 domain? We have several hundred machines, so an automated process would be best. Thank you in advance, T ...

How do I reconcile two sheets with in a .xls book?
How do I reconcile two sheets with in a .xls book? momy2cmt wrote: > How do I reconcile two sheets with in a .xls book? define reconcile gls858 If by reconcile, you are referring to values within cells and that th two sheets are identical in format, on a 3rd sheet enter a formula fo example, in cell A1 "=Sheet1!A1-Sheet2A1". If the result is not zero then there are different amounts in the cells. Copy the formula acros the range you wish to compare to highlight the variances. Does this answer your question -- Stuart Far ------------------------------------------------------...

help with outer join
I thought I knew how to do this, but for some reason it's not working - I could use some help. I have two tables: MSTR_TEST - a master list of available tests, key is testID (testID, testname) USER_TEST - one record per user, per test, IF test has been taken. Key is userID. (userID, testID, score) I want to create a query that will show me a SET of testIDs per user that exists in the User_Test table. If there is a record for any particular testID, I want to show the details for that record, but if there is no record for that user, for that particular test, I want to show a recor...

Formula for calc diff between two julian dates
Looking for the formula for calc diff between two julian dates. Worksheet is as follows: J-2 = order date K-2 = receive date L-2 = Lag time :confused -- saltr ----------------------------------------------------------------------- saltrm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2862 View this thread: http://www.excelforum.com/showthread.php?threadid=48288 DateDiff("d", "J2", "K2") "saltrm" wrote: > > Looking for the formula for calc diff between two julian dates. > > Worksheet is as follows: >...

Running two different Great Plains Client installs on Terminal Server?
We have two companies using the same Great Plains database and the client side requires different modifications in each company. Our thought was to create and maintain two different client installs for each company to keep the customizations separate. Does anyone have experience doing this on Terminal Server? What type of modifications? Forms,Reports? If that is it then just create a different Launch path and create a shortcut for the new launch and point the Dynamics.set files to the modified forms or reports for that particular company. >-----Original Message----- >We have...

Joining front end and back end?
I have been given a redundant database "to see if there are any useful bits in it". But it is has been split into front and back ends. How do I create the links to make it work? Thanks Little Crow On Jul 21, 12:06 pm, CW <C...@discussions.microsoft.com> wrote: > I have been given a redundant database "to see if there are any useful bits > in it". But it is has been split into front and back ends. How do I create > the links to make it work? > Thanks > Little Crow Tools, Utilities, Linked Table Manager... and then select the source for the backen...