sql problem with sub queries

[Jean-Paul]

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

 

[Thomas]

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

 

[Bob Barrows [MVP]]

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

 

[Bob Barrows [MVP]]

first of all, change query1 a bit. you don't wand to have a duplicate
column IdTypeItem.

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

 

[Thomas]

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

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