"inner SELECT"

Hi everybody!
I'm brand new to MS SQL Server,
Can you give me some examples of "Inner SELECT"?
Regards,
alias 939Nope.
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|||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

using Inner join when the field in my data table has the Null default value:

I have a datatable : Data_Table and a look up table: Lk_table. Myfield that Iuse in Inner Join is defined in both thedata and look table.

So I build my query like this:

SELECT * FROM dbo. Data_Table INNER JOIN

dbo. Lk_table ON dbo.Data_Table.MyField = dbo.Lk_table.Myfield

The pb, sometimes Ihave myfield still with its default null value in the datatable: Data_Table.So, I end up getting 0 record when I execute the query shown above.

How do I turn that around so that even if myfield in Data_Tableis Null, I still get the records from Data_Table. (I don t want a set ofrecords including all possible values from the look up table: Lk_Table)

Try to use LEFT join instead of inner join:

SELECT * FROM dbo. Data_Table LEFT JOIN dbo. Lk_table
ON dbo.Data_Table.MyField = dbo.Lk_table.Myfield

|||

Try to use LEFT join instead of inner join:

SELECT * FROM dbo. Data_Table LEFT JOIN dbo. Lk_table
ON dbo.Data_Table.MyField = dbo.Lk_table.Myfield

Or use such query:

SELECT * FROM dbo. Data_Table ,dbo. Lk_table
WHERE dbo.Data_Table.MyField = dbo.Lk_table.Myfield

OR dbo.Data_Table.MyField is null