SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Defining virtual column while query with joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

navyseal
Starting Member

Portugal
6 Posts

Posted - 06/28/2013 :  11:46:09  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/28/2013 :  12:03:42  Show Profile  Reply with Quote
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
Go to Top of Page

navyseal
Starting Member

Portugal
6 Posts

Posted - 06/28/2013 :  12:28:51  Show Profile  Reply with Quote
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.

Edited by - navyseal on 06/28/2013 12:30:10
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/28/2013 :  12:33:39  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

navyseal
Starting Member

Portugal
6 Posts

Posted - 06/28/2013 :  12:43:25  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/28/2013 :  12:50:51  Show Profile  Reply with Quote
sorry didnt get that. you want all as table2?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

navyseal
Starting Member

Portugal
6 Posts

Posted - 06/28/2013 :  13:09:40  Show Profile  Reply with Quote
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

Edited by - navyseal on 06/28/2013 13:11:34
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/28/2013 :  13:21:51  Show Profile  Reply with Quote
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
Go to Top of Page

navyseal
Starting Member

Portugal
6 Posts

Posted - 06/29/2013 :  06:58:27  Show Profile  Reply with Quote
that's not suppose to happen, but I guess it could return 'Multiple'
have you seen the image or did you tried the code?
Go to Top of Page

navyseal
Starting Member

Portugal
6 Posts

Posted - 07/01/2013 :  05:32:31  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/01/2013 :  05:58:54  Show Profile  Reply with Quote
You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000