Joins on 3 tables

Hi All,

I require to perfom a join on 3 tables within the same query . To explain myself better i have 3 tables

Main table

Label table

textbox table

The Main table contains the common fields in both the label and textbox table. While the label and textox table contain the fields that are sepcfic to them .

MAIN Table

pk Moduleid ItemName itemtype

36 372 test1 4 37 372 test2 4 38 372 test3 4 39 372 test4 6 40 372 test5 4

label

pk Main_fk labeltext

4 36 labeltext1 5 37 labeltext2 6 38 labeltext3 7 40 labeltext4

Textbox

pk Main_fk textboxtext

1 39 textbox1

I did infact manage to perform a join on these these tables.

Select * From tb_Main

inner join tb_Label

on tb_Main.pk = tb_Label.main_fk

where moduleID = @.moduleID

Select * From tb_Main

inner join tb_textbox

on tb_Main.pk = tb_textbox.main_fk

where moduleID = @.moduleID

The problem is that it returns two separate results . I require a join on the label and textbox table within the same query to return one result.

Is what im asking possible? I would appreciate if some exmaples are posted

I have no control on the design of the tables as i didnt create them but still if anyone has a suggestion on improving them please do ,so i can tell my colleague that they aren't designed well !!!!

Thanks in advance

Matt

Hai,

You can try this query, hope this will work.

DECLARE @.ModuleID int

SET @.ModuleID = 372

SELECT

*

FROM tb_Main AS M

JOIN tb_Label AS L

ON M.pk = L.main_fK

JOIN tb_textbox AS T

ON M.pk = T.main_fk

WHERE ModuleID = @.ModuleID

Regards,

Kiran.Y

|||

Thanks Y.Kiran for your suggestion.

I had already tried that out, it returns no rows.

Regards,

Matt

|||

Hai,

I didn't change any thing in the given query. Now I'm giving you the table definitions of all tables, Main, Label, TextBox. Check this one.

-- Create Main, Label, TextBox Tables.

CREATE TABLE tb_Main

( PK int,

Moduleid int,

ItemName varchar(50),

itemtype int,

CONSTRAINT PK_tb_Main_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

)

CREATE TABLE tb_Label

(

PK int,

Main_fk int CONSTRAINT FK_tb_Main_Main_fk REFERENCES tb_Main(PK),

labeltext varchar(50),

CONSTRAINT PK_tb_Label_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

)

CREATE TABLE tb_TextBox

(

PK int,

Main_fk int CONSTRAINT FK_tb_TextBox_Main_fk REFERENCES tb_Main(pk),

TextBoxText varchar(50),

CONSTRAINT PK_tb_TextBox_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

)

-- Insert data into Main, Label, TextBox tables.

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(36,372,'test1',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(37,372,'test2',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(38,372,'test3',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(39,372,'test4',6)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(40,372,'test5',4)

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(4,36,'labeltext1')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(5,37,'labeltext2')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(6,38,'labeltext3')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(7,40,'labeltext4')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(8,39,'labeltext5')

INSERT INTO tb_textbox(pk, Main_fk, textboxtext) VALUES(1,39,'textbox1')

-- Get the records based on the @.ModuleID variable.

DECLARE @.ModuleID int

SET @.ModuleID = 372

SELECT

*

FROM tb_Main AS M

JOIN tb_Label AS L

ON M.pk = L.main_fK

JOIN tb_textbox AS T

ON M.pk = T.main_fk

WHERE ModuleID = @.moduleID

Let me know, If I did any wrong.

Regards,

Kiran.Y

|||

You should be able to run the following statement to get what you need.

SELECT
tb_Main.pk, tb_ModuleID, tb_ItemName, tb_ItemType,
tb_Label.LabelText, tb_Textbox.Textboxtext
FROM
tb_Main
INNER_JOIN tb_Label ON tb_Main.pk = tb_Label.FK
INNER_JOIN tb_Textbox ON tb_Main.pk = tb_Textbox
WHERE tb_Main.ModuleID = @.ModuleID

The problem that I see is in your data. If this is a true representation of you data then an INNER JOIN is not going to return anything because when you join to the third table the only match you will find is 39, which is not in the second table, hence you get no results. If you were to do a LEFT OUTER JOIN, you could get all of the results with NULL values also represented. Try the query below to attempt to get results.

SELECT
tb_Main.pk, tb_ModuleID, tb_ItemName, tb_ItemType,
tb_Label.LabelText, tb_Textbox.Textboxtext
FROM
tb_Main
LEFT OUTER_JOIN tb_Label ON tb_Main.pk = tb_Label.FK
LEFT OUTER_JOiN tb_Textbox ON tb_Main.pk = tb_Textbox
WHERE tb_Main.ModuleID = @.ModuleID

Results:

pk ModuleID ItemName ItemType LabelText TextBox
-- -- -- -- -
36 372 test1 4 labeltext1 NULL
37 372 test2 4 labeltext2 NULL
38 372 test3 4 labeltext3 NULL
39 372 test4 6 NULL textbox1
40 372 test5 4 labeltext4 NULL

Hope this helps. Anyone feel free to correct if there are any inaccuracies. I'm relatively new to SQL Server.

|||

No Data was returned from the query that Kumar provided, BECAUSE there is NO common data between all three tables.

MAIN Table

pk Moduleid ItemName itemtype

36 372 test1 4 37 372 test2 4 38 372 test3 4 39 372 test4 6 40 372 test5 4

label

pk Main_fk labeltext

4 36 labeltext1 5 37 labeltext2 6 38 labeltext3 7 40 labeltext4

Textbox

pk Main_fk textboxtext

1 39 textbox1

There is NO [Mail_fk] for 39 in the table [Label], therefore no matching link between [Textbox], [Label] and [Main]

You 'could' use LEFT JOIN in both of the joins to have a resultset that includes ALL rows from [MAIN] even if there is NO matching links in the other tables.

|||

DBaker,

Excellent explanation and corrected query!

|||

Thanks guys for your help it worked great !

Matt