Defining virtual column while query with joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Defining virtual column while query with joins

Author

Topic

navyseal
Starting Member

6 Posts

Posted - 2013-06-28 : 11:46:09

I'm trying to do a select over 3 tables.
The main table (table1) has an Id which is a FK in the other two
If we find a match on table2 or table 3 it overrides table 1 result.
At same time i want a virtual column (evtType) which let me know the origin of the data ( from which table it came)
this is my code.

Creating tables


create table IF NOT EXISTS table1(pkid INTEGER PRIMARY KEY AUTOINCREMENT, descriptive TEXT);

INSERT INTO table1(descriptive) VALUES ("hate1");
INSERT INTO table1(descriptive) VALUES ("hate2");
INSERT INTO table1(descriptive) VALUES ("hate3");
INSERT INTO table1(descriptive) VALUES ("hate4");
INSERT INTO table1(descriptive) VALUES ("hate5");


create table IF NOT EXISTS table2(mainId INTEGER PRIMARY KEY AUTOINCREMENT, secId INTEGER, descriptive2 TEXT,
FOREIGN KEY ([secId]) REFERENCES [table1] ([pkid])
                                      );
INSERT INTO table2(secId, descriptive2) VALUES (2, "love1");
INSERT INTO table2(secId, descriptive2) VALUES (2, "love2");
INSERT INTO table2(secId, descriptive2) VALUES (1, "love3");
INSERT INTO table2(secId, descriptive2) VALUES (4, "love4");
INSERT INTO table2(secId, descriptive2) VALUES (4, "love5");

create table IF NOT EXISTS table3(mainId2 INTEGER PRIMARY KEY AUTOINCREMENT, secId2 INTEGER, descriptive3 TEXT,
FOREIGN KEY ([secId2]) REFERENCES [table1] ([pkid])
                                      );

INSERT INTO table3(secId2, descriptive3) VALUES (3, "nothing");

And my select:


SELECT *, 'Table1' as evtType FROM table1 t1
left join(SELECT *, 'Table2' as evtType from table2) t2
on t1.pkid = t2.secId 
left join(SELECT *, 'Table3' as evtType from table3) t3
on t1.pkid = t3.secId2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 12:03:42

so what should be your output? you've multiple values existing per pk value so how should it be returned? in same or different rows?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

navyseal
Starting Member

6 Posts

Posted - 2013-06-28 : 12:28:51

different rows

so if these two tables have a fk to the same primary key in table 1, they should return 2 rows. (but that's another problem i was thinking to deal with later)
because i might have 5 registers on table 2 and three more in table 3 and i need to see them all in the return.

Excuse my english.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 12:33:39

[code]
SELECT *, COALESCE(t3.evtType,t2.evtType,'Table1') as evtType FROM table1 t1
left join(SELECT *, 'Table2' as evtType from table2) t2
on t1.pkid = t2.secId
left join(SELECT *, 'Table3' as evtType from table3) t3
on t1.pkid = t3.secId2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

navyseal
Starting Member

6 Posts

Posted - 2013-06-28 : 12:43:25

Awesome :) Thanks, that seems to solve the row question. :)

But my main problem is about the evtType column.
According with http://sqlfiddle.com/ all evtType's will return 'Table2'

any ideas on that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 12:50:51

sorry didnt get that. you want all as table2?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

navyseal
Starting Member

6 Posts

Posted - 2013-06-28 : 13:09:40

Im creating a column called evtType which isnt part of the tables im consulting.

The objective of this evtType column is to tell me from which table the information was over written.
When i join the results in 1 return i lose the information from which table the data came from.

SQL fiddle, which i believe to work without bugs, tells me that the rows coming from table1 and table3 have evtType = null

example:
if we have a row in table 1 which has no references in table2 or table3, should put evtType column = 'Table1'
if we have a row in table 1 which has a reference in table3, should have evtType column = 'Table3'

here's what i mean
http://img515.imageshack.us/img515/5261/dusx.png

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 13:21:51

thats exactly what expression COALESCE(t3.evtType,t2.evtType,'Table1') does. What if both table2 and table3 has data? in that case what you should return?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

navyseal
Starting Member

6 Posts

Posted - 2013-06-29 : 06:58:27

that's not suppose to happen, but I guess it could return 'Multiple'
have you seen the image or did you tried the code?

navyseal
Starting Member

6 Posts

Posted - 2013-07-01 : 05:32:31

Ok, seems to me i had two issues. First i Was using websql instead SQLite.
Secondly it forgot about the star from the select, which fetches all the columns from all tables, and creates duplicated evtType columns.
I was trying to avoid having to write each column at the select since I only wanted to exclude one.
I think I've just learned on how to use COALESCE.
Thank you so much for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-01 : 05:58:54

You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources