Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > sql problem with sub queries

Reply
Thread Tools

sql problem with sub queries

 
 
Jean-Paul
Guest
Posts: n/a
 
      03-22-2005
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





 
Reply With Quote
 
 
 
 
Thomas
Guest
Posts: n/a
 
      03-22-2005
first of all, change query1 a bit. you don't wand to have a duplicate column
IdTypeItem.

SELECT *
FROM T_Items
WHERE (idTypeItem = 'Inscription')

now that gives you query2:

SELECT *
FROM T_Perso LEFT JOIN
(SELECT *
FROM T_Items
WHERE (idTypeItem = 'Inscription')) query1
ON T_Perso.IdPerso = query1.IdPerso

this could be even further simplyfied so no subquery is needed:

SELECT T_Perso.*, T_Items.*
FROM T_Perso LEFT JOIN
T_Items ON T_Perso.IdPerso = T_Items.IdPerso AND T_Items.TypeItem =
'Inscription'

this should run without problems. though some people might say using .* is
not a good thing.

cheers,
thomas


"Jean-Paul" <(E-Mail Removed)> wrote in message
news:423ff1a1$0$1822$(E-Mail Removed) net.net...
> 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
>
>
>
>
>



 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      03-22-2005
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;
>
>
> 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.
>


Dammit. i just wasted my time answering this question in the .asp.db
newsgroup! Please do not multipost!

This is definitely a database-related
question so .asp.db was the perfect group in which to post it. Posting it
here as well did not increase your chances of getting an answer (most of us
subscribe to both groups). On the contrary, if somebody had taken his time
to answer it here, only to find that it was already resolved in the other
group, that person may have been annoyed enough to ignore any future posts
from you, thereby decreasing your chances of getting help in the future.
<hint>

There are times when you will not be sure which group is most appropriate
(again, this was not one of them), and you will want to post a question to
both groups. In that situation, you should use the cross-posting technique,
rather than posting the same message multiple times. To crosspost, put
a semicolon-delimited* list of the newsgroups to which you wish to post in
the To: header of your post and post it once. It, and any replies to it,
will appear in all the newsgroups in your list. So, if I reply in .asp.db,
my reply will also appear here in .asp.general.

* ... or whatever delimiter is recognized by your news


--
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"


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      03-22-2005
Thomas wrote:
> first of all, change query1 a bit. you don't wand to have a duplicate
> column IdTypeItem.
>

<snip>:
>
> SELECT T_Perso.*, T_Items.*
> FROM T_Perso LEFT JOIN
> T_Items ON T_Perso.IdPerso = T_Items.IdPerso AND
> T_Items.TypeItem = 'Inscription'
>
> this should run without problems. though some people might say using
> .* is not a good thing.


Well, duh! You just repeated the mistake you advised against in your first
sentence: this query is returning the IdPerso twice.

Bob
--
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"


 
Reply With Quote
 
Thomas
Guest
Posts: n/a
 
      03-22-2005
of course. but as i don't know the fields he needs, what would you suggest?


guess the principle "give a little, get a little" could be applied to the
posters question hehe.

anyway, saw your post about wrong newsgroup post, so lets cut it here.

- thomas


"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thomas wrote:
>> first of all, change query1 a bit. you don't wand to have a duplicate
>> column IdTypeItem.
>>

> <snip>:
>>
>> SELECT T_Perso.*, T_Items.*
>> FROM T_Perso LEFT JOIN
>> T_Items ON T_Perso.IdPerso = T_Items.IdPerso AND
>> T_Items.TypeItem = 'Inscription'
>>
>> this should run without problems. though some people might say using
>> .* is not a good thing.

>
> Well, duh! You just repeated the mistake you advised against in your first
> sentence: this query is returning the IdPerso twice.
>
> Bob
> --
> 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"
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      03-22-2005
Thomas wrote:
> of course. but as i don't know the fields he needs, what would you
> suggest?
>
> guess the principle "give a little, get a little" could be applied to
> the posters question hehe.
>
> anyway, saw your post about wrong newsgroup post, .


ooh1 Not "wrong" newsgroup: if he had ONLY posted here, I would have had no
issue.

> so lets cut it here

No need to cut it here: this is a separate conversation.

I usually handle this the way I handled it in my reply in the .db newsgroup:

Select p.IdPerso , p.<other fields>, i.IdTypeItem,
i.<other fields - do not reselect IdPerso>
FROM T_Perso p LEFT JOIN T_Items i
ON p.IdPerso = i.IdPerso
WHERE i.IdTypeItem='Inscription'

I go out of my way to avoid using selstar in my newsgroup replies to avoid
giving the impression that I endorse its use.

Bob
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Death To Sub-Sub-Sub-Directories! Lawrence D'Oliveiro Java 92 05-20-2011 06:50 AM
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
Recognising Sub-Items and sub-sub items using xslt Ben XML 2 09-19-2007 09:35 AM
xslt queries in xml to SQL queries Ian Roddis Python 3 02-26-2006 06:49 PM
so many queries within queries I'm confused Abby Lee ASP General 11 08-06-2004 07:56 PM



Advertisments