SQL sub query problem

Hi,

This "query2" is used in an MS Access environment.

SELECT T_Perso.*, query1.*

FROM T_Perso LEFT JOIN query1 ON T_Perso.IdPerso = query1.IdPerso;



In SQL, "query1" is :

SELECT T_Items.*, T_Items.IdTypeItem

FROM T_Items

WHERE (((T_Items.IdTypeItem)="Inscription"));



I would like to translate "query2" in full SQL language in order to use it
in ASP but I have some problems with syntax and sub-queries.



I suppose it's quite easy but.I'm a newbie in SQL.

Thanks for your help



Jean-Paul

Re: SQL sub query problem

Jean-Paul wrote:
> Hi,
>
> This "query2" is used in an MS Access environment.
>
> SELECT T_Perso.*, query1.*
>
> FROM T_Perso LEFT JOIN query1 ON T_Perso.IdPerso = query1.IdPerso;

This can be run as-is from ASP (although I would get rid of the *'s and
explicitly name the fields to be returned - see
http://www.aspfaq.com/show.asp?id=2096):

set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=c:\path\to\database.mdb"
set rs=createobject("adodb.recordset")
cn.query2 rs

>
>
>
> In SQL, "query1" is :
>
> SELECT T_Items.*, T_Items.IdTypeItem.

See, here's the problem with using *. you are selecting the IdTypeItem field
TWICE (run the query and look at the results). Very bad practice.


>
> FROM T_Items
>
> WHERE (((T_Items.IdTypeItem)="Inscription"));
>
>
>
> I would like to translate "query2" in full SQL language in order to
> use it in ASP but I have some problems with syntax and sub-queries.
>
>
As stated above, you do not have to do this, (in fact, since you already
have a saved query, it is definitely recommended, at least by me, that you
do NOT do this), however:

When multiple queries and tables are ivolved, it is a good idea to use table
aliases. In this case, you do not even have to worry about subqueries. Just
do a 2-table join. like this:

Select p.IdPerso , p., i.IdTypeItem,
i.
FROM T_Perso p LEFT JOIN T_Items i
ON p.IdPerso = i.IdPerso
WHERE i.IdTypeItem='Inscription'


Bob Barrows

PS. Read these posts for more food for thought:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SQL sub query problem

And here's a little something I've put together about using subqueries
in ASP if that hopefully helps at all.

Example of one query (QueryB) based on the results of another query
(QueryA):

QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" &
QueryA & ")"

But the following is even faster and allows for more than one field to
be returned in QueryA:

QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS
tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

So QueryA would include all the CustID's for customers starting with A.

And QueryB would include more fields in the customers table (i.e. not
just the CustID field) for the records returned in QueryA (which was the
customers starting with A).

I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and build
from the tblSQLA on the left to other tables that have fields you want
to return.

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex http://www.developersdex.com ***