Results 1 to 3 of 3

Thread: "inner SELECT"

  1. #1
    Join Date
    Feb 2003
    Location
    Hanoi, VIETNAM
    Posts
    1

    Cool Unanswered: "inner SELECT"

    Hi everybody!

    I'm brand new to MS SQL Server,

    Can you give me some examples of "Inner SELECT"?

    Regards,
    alias 939

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Nope.
    Select
    or
    Inner join

    No such thing as inner select.

    It's covered well with examples in bol.

    select a.col1, b.col2
    from tbl1 a
    inner join tbl2 b
    on a.pk = b.fk

  3. #3
    Join Date
    Mar 2003
    Posts
    2

    Cool Re: "inner SELECT"

    SELECT will retireve your data from the DB. The effective format is "SELECT <What - Field Name or Names separated by comma> FROM <Which DB/Table> WHERE <Conditions for filtering results, such as particular field = something> ORDER BY <How you want it sorted when it;s output>"

    On the other hand, JOIN is used when you need to work with data across two or more tables. The effective format is "SELECT <What> FROM <First DB/Table Name> JOIN <Second DB/Table Name> ON <Condition that links the first and second table together> WHERE <Condition for filtering the results> ORDER BY <Sorting order>

    For example, I have one table called EMPLOYEES that holds ID, NAME, POSITION_ID and another table called POSITIONS that holds ID, POSITION_NAME. Now, the POSITION_ID in the EMPLOYEES table refers to the ID in the POSITIONS table. So, to get the list of all employee names and their positions, I would write:

    SELECT NAME, POSITION_NAME
    FROM EMPLOYEES
    JOIN POSITIONS ON EMPLOYEES.POSITIONID = POSITIONS.ID
    ORDER BY NAME, POSITION_NAME

    Notice I have skipped WHERE altogether (WHERE and ORDER BY are optional. Ther are plenty more options, see help)

    Also notice my SELECT statement contains field names from both tables - possible thanks to JOIN-ing them in the third row.

    Note also that if field names repeat in the joined tables you need to clarify your reference. For example, I could have written the above as:

    SELECT EMPLOYEES.NAME, POSITIONS.POSITION_NAME
    FROM ...

    Of course, this could be tedious, right? So what you can do is to assign alias to the tables / databases you deal with. In the above example:

    SELECT E.NAME, P.POSITION_NAME
    FROM EMPLOYEES E
    JOIN POSITIONS P ON E.POSITIONID = P.ID
    ORDER BY NAME, POSITION_NAME

    Notice how I put EMPLOYEES E and POSITIONS P in the above command. Effectively I assigned "E" to be alias for EMPLOYEES and "P" to be alias for POSITIONS, and so in the futre I only need to refer to these tables via this alias.

    Finally, to make the thing complete, you can have more than one type of JOIN - INNER JOIN, LEFT JOIN, RIGHT JOIN and OUTER JOIN. I'll let you browse through help for these.

    Hope this helps.

    Cheers,

    V

    Originally posted by thebitwriter
    Hi everybody!

    I'm brand new to MS SQL Server,

    Can you give me some examples of "Inner SELECT"?

    Regards,
    alias 939

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •