CODEWITHSUNDEEP
sqlsql-server-2008
Haroon A.
Haroon A.

Reputation: 371

Select from multiple tables in SQL Server in C#

I want to write SQL query which select some columns from the first table and all columns from the second table.

ID is the PK in table1 and id is the FK in table2.

Is this correct:

SELECT ID, table2.ALL
FROM table1
INNER JOIN table2 ON table1.ID = table2.id 
WHERE table1.ID= x AND table1.Column3 = 'y'

Upvotes: 1

Views: 6785

Answers (4)

marc_s
marc_s

Reputation: 754438

Do you have a column called ALL in table2 or do you want to select all columns from table2 ??

If you want all columns use table2.* instead:

SELECT table1.ID, table2.*
FROM table1 
INNER JOIN table2 ON table1.ID = table2.id 
WHERE table1.ID= x AND table1.Column3 = 'y'

Also, since you have ID in both tables, you need to specify which one to select in your statement.

Update: if you don't want all columns from table2, then you need to explicitly specify the columns that you do want:

SELECT 
    t1.ID, 
    t2.column1, t2.column2, ....., t2.columnN
FROM table1 t1 
INNER JOIN table2 t2 ON t1.ID = t2.id 
WHERE t1.ID= x AND t1.Column3 = 'y'

Upvotes: 4

Nathan Anderson
Nathan Anderson

Reputation: 6878

Instead of "ALL", use *: 

SELECT t1.ID, t2.* FROM table1 as t1
INNER JOIN table2 as t2 
ON t1.ID = t2.id 
WHERE t1.ID = x 
AND t1.Column3 = 'y'

Upvotes: 1

Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

You can't use table2.ALL. That will look for a column called "All" in table2. You want to use table2.* instead.

SELECT table1.ID, table2.*
FROM   table1 INNER JOIN table2 on table1.ID = table2.id
WHERE  table1.ID = x AND table1.Column3 = 'y'

Upvotes: 3

aF.
aF.

Reputation: 66697

Do it like this:

select t1.ID, t2.*
from table1 t1
inner join table2 t2 on table1.ID = table2.id
WHERE t1.ID=xxx AND t1.Column3 = 'y'

Replace xxx by the ID that you want.

Upvotes: 0

Related Questions