Advanced SQL Programming				 Fall 2002

		"Real-world" problems, Oct. 14th

1.  I have a table called 'tbl_business_details' and one called
'tbl_business_listings'.  The key between the two tables is a numeric
field called 'detid'.  I want to retrieve only the records that are
not in both tables.  When I set up the query using

tbl_business_details.detid <> tbl_business_listings.detid

I get a list of records where those not in both tables are listed TWICE
and those in both tables are listed ONCE.  What am I doing wrong?  How
should I set up the SQL query?
================================
Response in the Internet discussion group:

If I understand correctly,
looking at your query above in design view,
select the join line between your tables
and change join property to a Left Outer Join (#2).

Drag and drop [detid] from tbl_business_details
down to a field row in the grid (don't select Show checkbox).

In Criteria row for this field column, type

Is Null

SQL will look like:

SELECT [tbl_business_listings].[AField]
FROM tbl_business_listings LEFT JOIN tbl_business_details ON
[tbl_business_listings].[detid]=[tbl_business_details].[detid]
WHERE ([tbl_business_details].[detid] Is Null);

This will return all the records in tbl_business_listings
that do not have a match in tbl_business_details.

Make another query using same process to return
all records in tbl_business_details
that do not have a match in tbl_business_listings.

its SQL will look like:

SELECT [tbl_business_details].[AField]
FROM tbl_business_details LEFT JOIN tbl_business_listings ON
[tbl_business_details].[detid]=[tbl_business_listings].[detid]
WHERE ([tbl_business_listings].[detid] Is Null);

You can combine these 2 queries in a UNION query
to "retrieve the records that are not in both tables."

NOTE:  To test in Syabase, try to list all "customers" who have not
placed an order (by name) together with some "anonymous" orders
(associated with a customer number but not with a name).  Outer join
syntax will be different using Transact-SQL. 

=========================================================================
2.  Why does the following query return "OK" if there are no matching
records in Table 1 (no record where Field1=2) but nothing if there are
no records AT ALL in Table1?  I was expecting OK in both cases:

SELECT "OK" as Result
FROM Table1
WHERE NOT EXISTS
   (SELECT *
    FROM Table1
    WHERE Table1.Field1=2)
==========================================
Response in the Internet discussion group:

Because

SELECT  "OK"
FROM Table1
WHERE True

will return nothing, if there is no record at all in Table1.

If you prefer, if there is no field where Table1.Field1=2, then

 SELECT "OK" AS RESULT
 FROM Table1
 WHERE NOT EXISTS (SELECT * FROM Table1 WHERE (Table1.Field1=2));

will return "n" records with "OK", where n=SELECT COUNT(*) FROM Table1,
the number of records in table1. So, I assume you tested with n=1, one,
record in table, but then, it doesn't return "OK", but one record (n=1), 
with "OK" ... and if  you have n=0, then you have no, zero, record.

=======================================================================
3.  In my codetable I have a field with a unique code which has 7
characters (6 text and 1 numeric), e.g., syzcas6, thymul4, etc.  The
last character(numeric) relates to the size of the product.

I have another table with no relationships that the contains the sizes
and the codes (sizetable).

E.g.:

Codetable		Code
			Syzcas6
			Thymul4

Sizetable		Code		Size
			4		100mm
			6		150mm
			8		200mm

How do I create a query that examines the code and returns the correct
size?
==========================
Response in the Internet discussion group:

If you really want to do this then the SQL is as follows:

SELECT Codetable.code, Sizetable.size
FROM Codetable, Sizetable
WHERE right(Codetable.code,1)=Sizetable.sizecode;

A better approach would be to either split the code into 2 columns or at
least duplicate the last character of the code in a separate column.
You could then create a relationship, maintain referential integrity, etc.
==========================================================================
4.  Given a Table of people who donate money.  Two of the fields are
Name and Amount.  For each Name that has any single donation >100, would
like to display all records containing that Name.

 Example:  Given the Table:
    John  		80
    Sue   		20
    John 		10
    Sue  		120
    Sue   		30

    Desired recordset:
    Sue   		20
    Sue   		30
    Sue 		120

 This  SQL only pulls "Sue  120":   SELECT * FROM Table SORT BY Name,
Amount WHERE Amount > 100

 I know this is wrong; I don't know how to modify it to select all "Sue"
records in the query?
=====================
Response in an Internet MS Access discussion group:

Name is a reserved word in Jet SQL so best not to use it:

SELECT donor_name, amount
    FROM donations
    WHERE donor_name IN
        (SELECT donor_name
            FROM donations
            WHERE amount > 100)

I've used the IN syntax here because it's more intuitive. An alternative
construct could be the EXISTS predicate.

SELECT d1.donor_name, d1.amount
    FROM donations AS d1
    WHERE EXISTS
        (SELECT *
            FROM donations as d2
            WHERE d2.amount > 100
            AND d2.donor_name = d1.donor_name)