SQL Dummy

[jim]

Hi,

I have two queries:

qReg:

SELECT tblRegionalExpe rience.Consulta ntID, tblRegionalExpe rience.RegionID
FROM tblRegionalExpe rience
WHERE (((tblRegionalE xperience.Regio nID)=1));
qLang:

SELECT tblLanguageSkil ls.ConsultantID , tblLanguageSkil ls.LanguageID
FROM tblLanguageSkil ls
WHERE (((tblLanguageS kills.LanguageI D)=1));
i have these joined together in qAll:

SELECT qLang.Consultan tID, qLang.LanguageI D, qReg.Consultant ID,
qReg.RegionID
FROM qLang INNER JOIN qReg ON qLang.Consultan tID = qReg.Consultant ID;

but i would like be able to write qAll without reference to qReg or
qLang. my efforts so far have been hopeless any help much appreciated.

[Pachydermitis]

You can use what we call derived tables - which are just sub queries -
as long as you have at least Access 2000.
qry a: Select a from x
qry b: Select b From y
qAll Select a,b From (Select a from x) as aliasA, (Select b From y) as
Aliasb

AliasA & AliasB are just a names I am giving the derived tables, call
them whatever you want as long as it is not already being used
somewhere in the qry. Rather than using the letter t prefix I use for
the tables, I use an a (eg. aCustomers)

There are a couple issues.
1) Once this is created "if" you go back into the sql of the qry,
access messes up the syntax for you and changes your () on the derived
tables to [] so you have to change them back.
2) Access doesn't like to have these as the recordsource for forms or
reports - you will have problems unless you save it as a qry and then
use the qry as the recordsource.
3) It's hellacious to debug your sql.

Other than that I use them all the time.

Good luck
Pachydermitis

SELECT aLang.Consultan tID, aLang.LanguageI D, aReg.Consultant ID,
aReg.RegionID
FROM (SELECT tblLanguageSkil ls.ConsultantID ,
tblLanguageSkil ls.LanguageID
FROM tblLanguageSkil ls
WHERE (((tblLanguageS kills.LanguageI D)=1))) as aLang INNER JOIN (SELECT
tblRegionalExpe rience.Consulta ntID, tblRegionalExpe rience.RegionID
FROM tblRegionalExpe rience
WHERE (((tblRegionalE xperience.Regio nID)=1))) as aReg ON
aLang.Consultan tID = aReg.Consultant ID;
jim wrote:

Hi,

I have two queries:

qReg:

SELECT tblRegionalExpe rience.Consulta ntID, tblRegionalExpe rience.RegionID
FROM tblRegionalExpe rience
WHERE (((tblRegionalE xperience.Regio nID)=1));
qLang:

SELECT tblLanguageSkil ls.ConsultantID , tblLanguageSkil ls.LanguageID
FROM tblLanguageSkil ls
WHERE (((tblLanguageS kills.LanguageI D)=1));
i have these joined together in qAll:

SELECT qLang.Consultan tID, qLang.LanguageI D, qReg.Consultant ID,
qReg.RegionID
FROM qLang INNER JOIN qReg ON qLang.Consultan tID = qReg.Consultant ID;

but i would like be able to write qAll without reference to qReg or
qLang. my efforts so far have been hopeless any help much appreciated.

[deluxeinformation]

jim wrote:

Hi,

I have two queries:

qReg:

SELECT tblRegionalExpe rience.Consulta ntID, tblRegionalExpe rience.RegionID
FROM tblRegionalExpe rience
WHERE (((tblRegionalE xperience.Regio nID)=1));
qLang:

SELECT tblLanguageSkil ls.ConsultantID , tblLanguageSkil ls.LanguageID
FROM tblLanguageSkil ls
WHERE (((tblLanguageS kills.LanguageI D)=1));
i have these joined together in qAll:

SELECT qLang.Consultan tID, qLang.LanguageI D, qReg.Consultant ID,
qReg.RegionID
FROM qLang INNER JOIN qReg ON qLang.Consultan tID = qReg.Consultant ID;

but i would like be able to write qAll without reference to qReg or
qLang. my efforts so far have been hopeless any help much appreciated.

SELECT tblLanguageSkil ls.ConsultantID ,
tblLanguageSkil ls.LanguageID,
tblRegionalExpe rience.Consulta ntID,
tblRegionalExpe rience.RegionID
FROM tblRegionalExpe rience
INNER JOIN tblLanguageSkil ls
ON tblRegionalExpe rience.Consulta ntID = tblLanguageSkil ls.ConsultantID
WHERE (tblRegionalExp erience.RegionI D=1) AND
(tblLanguageSki lls.LanguageID= 1)

should do what you want, but there's really no reason to include both
tblLanguageSkil ls.ConsultantID and tblRegionalExpe rience.Consulta ntID
since these should be equal because of your ON clause.

Bruce

[Pachydermitis]

Bruce,
your method could produce a large carteisan product based on the other
records in the table, and skew the results.

de************* **@gmail.com wrote:

jim wrote:

Hi,

I have two queries:

qReg:

SELECT tblRegionalExpe rience.Consulta ntID, tblRegionalExpe rience.RegionID
FROM tblRegionalExpe rience
WHERE (((tblRegionalE xperience.Regio nID)=1));
qLang:

SELECT tblLanguageSkil ls.ConsultantID , tblLanguageSkil ls.LanguageID
FROM tblLanguageSkil ls
WHERE (((tblLanguageS kills.LanguageI D)=1));
i have these joined together in qAll:

SELECT qLang.Consultan tID, qLang.LanguageI D, qReg.Consultant ID,
qReg.RegionID
FROM qLang INNER JOIN qReg ON qLang.Consultan tID = qReg.Consultant ID;

but i would like be able to write qAll without reference to qReg or
qLang. my efforts so far have been hopeless any help much appreciated.

SELECT tblLanguageSkil ls.ConsultantID ,
tblLanguageSkil ls.LanguageID,
tblRegionalExpe rience.Consulta ntID,
tblRegionalExpe rience.RegionID
FROM tblRegionalExpe rience
INNER JOIN tblLanguageSkil ls
ON tblRegionalExpe rience.Consulta ntID = tblLanguageSkil ls.ConsultantID
WHERE (tblRegionalExp erience.RegionI D=1) AND
(tblLanguageSki lls.LanguageID= 1)

should do what you want, but there's really no reason to include both
tblLanguageSkil ls.ConsultantID and tblRegionalExpe rience.Consulta ntID
since these should be equal because of your ON clause.

Bruce

[jim]

Thanks guys, have done some reading on sub queries and managed to make
some real progress with this, which i have been struggling with for over
a week.... many thanks for your input...

Pachydermitis wrote:

Bruce,
your method could produce a large carteisan product based on the other
records in the table, and skew the results.

de************* **@gmail.com wrote:

>jim wrote:

>>Hi,

I have two queries:

qReg:

SELECT tblRegionalExpe rience.Consulta ntID, tblRegionalExpe rience.RegionID
FROM tblRegionalExpe rience
WHERE (((tblRegionalE xperience.Regio nID)=1));
qLang:

SELECT tblLanguageSkil ls.ConsultantID , tblLanguageSkil ls.LanguageID
FROM tblLanguageSkil ls
WHERE (((tblLanguageS kills.LanguageI D)=1));
i have these joined together in qAll:

SELECT qLang.Consultan tID, qLang.LanguageI D, qReg.Consultant ID,
qReg.Region ID
FROM qLang INNER JOIN qReg ON qLang.Consultan tID = qReg.Consultant ID;

but i would like be able to write qAll without reference to qReg or
qLang. my efforts so far have been hopeless any help much appreciated.

SELECT tblLanguageSkil ls.ConsultantID ,
tblLanguageSki lls.LanguageID,
tblRegionalExp erience.Consult antID,
tblRegionalExp erience.RegionI D
FROM tblRegionalExpe rience
INNER JOIN tblLanguageSkil ls
ON tblRegionalExpe rience.Consulta ntID = tblLanguageSkil ls.ConsultantID
WHERE (tblRegionalExp erience.RegionI D=1) AND
(tblLanguageSk ills.LanguageID =1)

should do what you want, but there's really no reason to include both
tblLanguageSki lls.ConsultantI D and tblRegionalExpe rience.Consulta ntID
since these should be equal because of your ON clause.

Bruce

[deluxeinformation]

Pachydermitis wrote:

Bruce,
your method could produce a large carteisan product based on the other
records in the table, and skew the results.

A Cartesian product would be the result of a cross join, not an inner
join, in that a Cartesian product returns all rows from the right side
of a join for each row in the left side. I'm not seeing a situation in
which our two queries would produce different results. If the intent
is to return a set of consultants from a specified region with a
specified language skill then both queries do this. However, a simple
select without subqueries suffices for this task as best I can tell.
That being said, I'm always up for learning something new or being
shown the error of my ways. I really just don't see the necessity of
subqueries for this. I think we've simply reversed the order in which
we are retrieving our results. Your subqueries first filter each table
separately and then perform a join. I simply join first and then
filter. Please let me know if I am overlooking something. It wouldn't
be the first time :)

Bruce

[Pachydermitis]

Hi Bruce,
I hope that I did not offend you at all. Since we don't know all the
data in the tables and how it relates to other tables, I was answering
the question in a way that I was sure would not have any undesired
results.

You can also get an cartesian product if you are joining fields that
are not primary keys. for example:
Table1
id prod Cust Date
1 123 2 1/1/2001
2 456 2 1/1/2001
3 123 1 2/1/2001
4 123 2 2/1/2001

Table2
ID cust inv amt
22 1 555 5
23 2 666 10
24 2 777 5

SELECT Table1.Cust, Sum(Table2.amt) AS SumOfamt, count(prod) as
CountOfProd
FROM Table1 INNER JOIN Table2 ON Table1.Cust = Table2.cust
GROUP BY Table1.Cust;

the invoice total for cust 2 will be 45 when it should be 15 because we
need other joins or sub queries.

SELECT aTable1.cust AS Expr1, Sum(Table2.amt) AS SumOfamt,
Count(aTable1.p rod) AS CountOfprod
FROM (select cust, count(Table1.pr od) as prod FROM Table1 group by
cust) AS aTable1 INNER JOIN Table2 ON aTable1.cust = Table2.cust
GROUP BY aTable1.cust;

will give us the correct numbers.

Another time derived tables can be a life saver is when you have huge
recordsets that you are joining. Say you have two tables that have 5
million rows each. If you can limit each with a derived table, then
join the smaller sets, you can speed up your data pull quite a bit. 5
million won't fly in access, but on some of the more robust engines I
have run into that scenario.

That being said I also may be overlooking something, and love this
forum because of all the stuff I learn from it. :)

P

de************* **@gmail.com wrote:

Pachydermitis wrote:

Bruce,
your method could produce a large carteisan product based on the other
records in the table, and skew the results.

A Cartesian product would be the result of a cross join, not an inner
join, in that a Cartesian product returns all rows from the right side
of a join for each row in the left side. I'm not seeing a situation in
which our two queries would produce different results. If the intent
is to return a set of consultants from a specified region with a
specified language skill then both queries do this. However, a simple
select without subqueries suffices for this task as best I can tell.
That being said, I'm always up for learning something new or being
shown the error of my ways. I really just don't see the necessity of
subqueries for this. I think we've simply reversed the order in which
we are retrieving our results. Your subqueries first filter each table
separately and then perform a join. I simply join first and then
filter. Please let me know if I am overlooking something. It wouldn't
be the first time :)

Bruce