sql problem with sub queries

Discussion in 'ASP General' started by Jean-Paul, Mar 22, 2005.

  1. Jean-Paul

    Jean-Paul Guest

    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
     
    Jean-Paul, Mar 22, 2005
    #1
    1. Advertising

  2. Jean-Paul

    Thomas Guest

    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" <> wrote in message
    news:423ff1a1$0$1822$...
    > 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, Mar 22, 2005
    #2
    1. Advertising

  3. 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"
     
    Bob Barrows [MVP], Mar 22, 2005
    #3
  4. 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"
     
    Bob Barrows [MVP], Mar 22, 2005
    #4
  5. Jean-Paul

    Thomas Guest

    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]" <> wrote in message
    news:...
    > 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"
    >
     
    Thomas, Mar 22, 2005
    #5
  6. 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.
     
    Bob Barrows [MVP], Mar 22, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,513
    Crutcher
    Feb 26, 2006
  2. Ben
    Replies:
    2
    Views:
    901
  3. ecoolone
    Replies:
    0
    Views:
    765
    ecoolone
    Jan 3, 2008
  4. Lawrence D'Oliveiro

    Death To Sub-Sub-Sub-Directories!

    Lawrence D'Oliveiro, May 5, 2011, in forum: Java
    Replies:
    92
    Views:
    2,044
    Lawrence D'Oliveiro
    May 20, 2011
  5. Abby Lee

    so many queries within queries I'm confused

    Abby Lee, Aug 4, 2004, in forum: ASP General
    Replies:
    11
    Views:
    356
    Aaron [SQL Server MVP]
    Aug 6, 2004
Loading...

Share This Page