June 18, 2012 at 3:28 am
Hi Experts
Whats the most efficient way of performing this query:
Give me all records from T1 if there is no join on T2, else just return records where they exist in both tables?
IF ISNULL(OBJECT_ID('dbo.T2'),'')!='' DROP TABLE T2
IF ISNULL(OBJECT_ID('dbo.T1'),'')!='' DROP TABLE T1
CREATE TABLE T1 (
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
Field1 VARCHAR(10) NOT NULL
)
CREATE TABLE T2 (
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
T1id INT REFERENCES T1(id) NOT NULL,
Field2 VARCHAR(10) NOT NULL
)
INSERT INTO T1
SELECT 'Widget1' UNION ALL
SELECT 'Widget2' UNION ALL
SELECT 'Widget3'
INSERT INTO T2
SELECT 1, 'Company1'
SELECT
t1.id,
t1.Field1,
T2.Field2
FROM T1
LEFT JOIN T2 ON T1.id = T2.T1id
Thanks
Nick
June 18, 2012 at 3:35 am
Give me all records from T1 if there is no join on T2, else just return records where they exist in both tables?
What? Return records from T1 only or from T1 and T2? May be do you wnat simple UNION?
Could you please clarify what do you really want to see in results based on supplied setup?
June 18, 2012 at 4:10 am
What im trying to say is....
When T2 has no records, bring back all records from T1, else if T2 does have a records - only bring back records from T1 where the join exists. As the query above shows, im bringing back Field1 and Field2 from both tables (although Field2 may be null)
June 18, 2012 at 4:22 am
-- return records from T1 if no records exist in T2
SELECT t1.id,
t1.Field1,
NULL as Field2
FROM T1
WHERE NOT EXISTS (SELECT 1 FROM T2)
UNION ALL
-- return records from T1 JOINED to T2
SELECT t1.id,
t1.Field1,
T2.Field2
FROM T1
JOIN T2 ON T1.id = T2.T1id
Are you sure that is what you want?
June 18, 2012 at 4:32 am
Well that certainly works, if I remove the record from T2 I get all records from T1, and if I put it back in I only get the one record I want.
The tables I used are just for illustraiton; the actual query I've been asked to implement this in is a lot more complex. As I imagined, theres no easy (cost efficient) way of doing this so i'll push back on the developer to look again at the requirements.
Thanks for your time.
Cheers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply