Two tables connected by foreign key

Question

I need to connect two tables in SQL Server: Table A has a primary key called IDTableA (integer field) and Table B has a primary key IDTableB (integer field as well).

Table A contains a foreign key IDTableB whereby I want to connect both tables, this field is of type integer and its value is 0.

My problem is that since there is no record in Table B whose ID is 0, do not show me those records from table A with a 0 stored in the foreign key.

The relationship between both tables is: a record in Table A may belong to one or no record in the table B. Therefore, the default value of the foreign key in table A is 0.

I tried connecting the tables with INNER JOIN and LEFT OUTER JOIN but it does not work and left no records show. What I can do?.

Thanks.

My SQL statement:

Select TableA.* 
From TableA 
inner join TableB on TableA.IdTableB = TableB.IdTableB

The Solution:

    Select TableA.* 
    From TableA 
    LEFT JOIN TableB on TableA.IdTableB = TableB.IdTableB

More Info: LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

Left join is correct. You probably switched sides. Can you please post your query? — Nikola Markovinović, Mar 13 '12 at 11:22
If the value of TableA.IDTableB is always 0, how do you expect it to connect to Table B (where there is no record whose ID is 0)? — , Mar 13 '12 at 11:23
Mark Bannister, default value or IdTableB is 0, but his value could be any integer value. — mggSoft, Mar 13 '12 at 11:27
The column in TableA referencing TableB should be nullable, and the default should be null, if you don't want to reference a row in TableB. — Damien_The_Unbeliever, Mar 13 '12 at 11:29
If you change "inner" to "left" your query will be fine. If you are still getting no rows, TableA is empty OR you have __where__ clause you did not share with us. — Nikola Markovinović, Mar 13 '12 at 11:33

score 1 · Accepted Answer · edited Jan 26 '20 at 22:11

1

Try to use left join only

Something like this

Select *
from table_1 tbl1
left join table_2 tbl2 on tbl2.id = tbl1.id

edited Jan 26 '20 at 22:11

Dharman

30,962
25
85
135

answered Mar 13 '12 at 11:25

joni_demon

656
6
12

If you post code, XML or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Mar 13 '12 at 11:26
Owkei,did you try it something like this "Select TableA.* From TableA left join TableB on TableA.IdTableB = TableB.IdTableB", – joni_demon Mar 13 '12 at 11:33
Sorry but I had tried `LEFT OUTER JOIN` but not `LEFT JOIN`. That was the solution. Now works properly. – mggSoft Mar 13 '12 at 11:34
@MGG_Soft It is the same. Keyword OUTER may be omitted. – Nikola Markovinović Mar 13 '12 at 11:36
Here is the information: [link](http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server) – mggSoft Mar 13 '12 at 11:44

score 0 · Answer 2 · answered Mar 13 '12 at 11:30

An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

FROM Table A <renametablehere>
Inner Join Table B <renametablehere>
On <renamedtablename for A>.ID = <renamed table name for b>B.ID

Two tables connected by foreign key

2 Answers2