Showing posts with label explain. Show all posts
Showing posts with label explain. Show all posts

Monday, March 26, 2012

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

Wednesday, March 7, 2012

Join tables help

Could somebody please explain to me how do we join a table onto itself as that is what I was advised to do but I can't quite get where I want to go.

What I want to do is list values from a table,but those values can be just a quote (what would cost if they decided to go for that option) or those values can represent what was spent and invoiced, what is confusing me is that all of that gets saved in the same table and in same columns, so what was quoted for example for AirFares and what was spent gets saved in the same record but when it is "quoted amount" then ID = 1 but when it is invoiced ID = -1 and that is how we know what was quoted and what was invoiced.

But I need to split that one field into two columns one showing AirFareQuoted and one AirFareInvoiced and i have no idea how to achieve this.

I hope this makes sense and somebody can help meHi, can you post some sample data with expected outcome?

Madhivanan|||select t1.option
, t1.AirFares as AirFareQuoted
, t2.AirFares as AirFareInvoiced
from yourtable as t1
left outer
join yourtable as t2
on t1.option = t2.option
and t2.ID = -1
where t1.ID = 1|||I think he'd want a FULL OUTER JOIN on this in case data is not present for one of the two options:

select coalesce(t1.option, t2.option) as option,
t1.AirFares as AirFareQuoted
t2.AirFares as AirFareInvoiced
from yourtable as t1
full outer join yourtable as t2
on t1.option = t2.option
where nullif(t1.ID, 1) = 1
and nullif(t2.ID, -1) = -1|||blindman, he didn't say so, but my business logic is that you can't create an invoice unless there was a quote, but you can have a quote without it leading to an invoice|||Don't talk to me about LOGIC, man! We're dealing with BUSINESS!

You ivory tower Canadians with your high-falutin "educations" and your "logic" are just out of touch with the way things are done down here in the Red States! We don't got no truck with that elitist stuff no more!|||blindman, he didn't say so, but my business logic is that you can't create an invoice unless there was a quote, but you can have a quote without it leading to an invoiceA retainer fee is a common occurance where there is an invoice without a quote or even any service/time/labor.

-PatP|||Don't talk to me about LOGIC, man! We're dealing with BUSINESS!

You ivory tower Canadians with your high-falutin "educations" and your "logic" are just out of touch with the way things are done down here in the Red States! We don't got no truck with that elitist stuff no more!You are starting to frighten me... Have you thought about a career in politics?

-PatP|||A retainer fee is a common occurance where there is an invoice without a quote or even any service/time/labor.whare kin ah git me one a them?

that sounds lahk jest the ticket fer my consultin bidness

i wanna be retained!!|||I got me one o' them retainers fer my overbite back when'st I was in high-school. Derned uncomfertable.|||You guys have just about lost your collective minds today...

Did someone spike the watercooler?

Post the DDL for the table...sounds like a normalization problem to me...|||Thanks guys for your replys, I will use this and see what I get.

I was going to post the view I am trying to create but there is too much to explain.|||...but there is too much to explain.i know that feeling|||I just love Lindman's obsession with coalesce, especially when there is no 3rd value...I guess trying to be cute is a very strong feeling...Trying to compensate for something? :D|||I just love Lindman's obsession with coalesce, especially when there is no 3rd value...I guess trying to be cute is a very strong feeling...Trying to compensate for something? :DYeah, but I'm rather fond of standard, portable ways like Coalesce() to do things too... I hate having to rewrite code every time I switch database engines again.

-PatP|||exactamundo

i shudder every time i see someone use NVL and ISNULL

it's not an obsession, rdjabarov, it's just smart coding

why, what do you use instead of coalesce?

:)|||I don't get all lit up about IsNull, Nvl, and other vendor specific extensions to SQL. Often those are the things that force the standards committee to act on a much needed construct for the standard instead of debating it until half past doomsday. If a vendor specific extension is the only practical way from where I'm at to where I want to be, I'll gladly use the extension, but if there's a standard way to get there, I'll prefer it almost every time!

-PatP|||Well, maybe it's because COALESCE is really misleading

BOL:

COALESCE
Returns the first nonnull expression among its arguments.

The actual Def

Definition: [v] mix together different elements; "The colors blend well"; "fuse the clutter of detail into a rich narrative"--A. Schlesinger
[v] fuse or cause to grow together

In DB2 it's COALESCE and VALUE(Col1,'Argument')

Oracle is NVL as well...

OK...I take it back...NVL, VALUE, ISNULL are totally separate functions..COALESCE Can accept MANY Values and the first one that is NOT NULL, or the absence of anything, wins.

So they do seem to be different...

babble..babble..bable...

lack of sleep..sorry|||It's true, I am obsessed with COALESCE. I dream about it every night, and it coalesce(haunts, infests) my every waking thought. I feel the coalesce(compulsion, desire, need) to use it coalesce(constantly, continually, incessantly).

WHY COALESCE()?!!! WHY! WHY!!!

Somebody please coalesce(help, aid, assist) me!

You crack me up rdjabarov. But happy nullif(christmas, hannukah) to you anyway. :)|||happy nullif(christmas, hannukah) to you anywaymy nomination for quote of the year

nice one, blindman

:)|||My apologies to all the Kwanzaa and Festivus celebrators out there, but NULLIF could only take two values... :)|||actually, you should really leave NULL as the last option, for those of us without any religious or societal holiday adherence|||Unnecessary. If both parameters are null, NULLIF() returns null...|||... what is confusing me is that all of that gets saved in the same table and in same columns, so what was quoted for example for AirFares and what was spent gets saved in the same record but when it is "quoted amount" then ID = 1 but when it is invoiced ID = -1 and that is how we know what was quoted and what was invoiced.
I hope this makes sense and somebody can help me

Maybe its much less confusing to you if you decide not to work with the table itself, but with two views separating your quotes and spents?! In practise:

CREATE VIEW Quotes AS SELECT * FROM <YourTable> WHERE ID = 1;
CREATE VIEW Spents AS SELECT * FROM <YourTable> WHERE ID = -1;

If there is a way to relate quotes to spends, you can join these two views using this relation in any (INNER, OUTER, FULL OUTER) way you want.

I hope this helps.