April 27, 2010 at 10:01 am
Thanks for your help! How can I include only records in the query below where A.Live = 1, B.Live = 1, and C.Live = 1.
I have tried using the and along with or operators in the where clause and all top A table alias records get excluded.
SELECT A.T1Name, A.T1ID, B.T2ID, B.T2Name, C.T3ID, C.T3Name
FROM Tier1 AS A LEFT OUTER JOIN
Tier2 AS B ON A.T1ID = B.T1IDFK LEFT OUTER JOIN
Tier3 AS C ON B.T2ID = C.T2IDFK
ORDER BY A.ButtonLoc, A.T1Name, B.ButtonLoc, B.T2Name, C.ButtonLoc, C.T3Name
April 27, 2010 at 10:13 am
If you would provide the DDL (CREATE TABLE statements) for the table(s) involved, sample data (series of INSERT INTO tablename statements) that represents the problem you are attempting to solve, expected results based on the sample data it would help us help you.
Please read the first article i reference below in my signature block for assistance on what I have requested.
Off the top of my head, I would add the A.Live = 1, B.Live = 1, and the C.Live = 1 in the appropriate ON criteria of the outer joins, but that is just a guess without the info I have reguested.
April 27, 2010 at 2:05 pm
Thanks for your help. Notice that I have the where clause commented out. When you remove the comments I do not get expected results. Live is a flag to determine content that is live. If it is Equal to 1 I want it to show.
DECLARE @TIER1 TABLE
(
T1ID INT,
T1Name NVARCHAR (255),
ButtonLoc INT,
live INT
)
INSERT INTO @tier1 (T1ID,T1Name,ButtonLoc,live)
VALUES (1,'Home',5,1);
INSERT INTO @tier1 (T1ID,T1Name,ButtonLoc,live)
VALUES (2,'About',10,1);
INSERT INTO @tier1 (T1ID,T1Name,ButtonLoc,live)
VALUES (3,'Regions',15,1);
DECLARE @TIER2 TABLE
(
T2ID INT,
T1IDFK INT,
T2Name NVARCHAR (255),
ButtonLoc INT,
live INT
)
INSERT INTO @tier2 (T2ID,T1IDFK, T2Name,ButtonLoc,live)
VALUES (49,2,'About Us',5,1);
INSERT INTO @tier2 (T2ID,T1IDFK, T2Name,ButtonLoc,live)
VALUES (2,2,'Staff',10,1);
INSERT INTO @tier2 (T2ID,T1IDFK, T2Name,ButtonLoc,live)
VALUES (3,2,'Contact US',15,1);
INSERT INTO @tier2 (T2ID,T1IDFK, T2Name,ButtonLoc,live)
VALUES (3,2,'Navigation Tips',15,0);
SELECT A.T1Name, A.T1ID, A.Live, B.T2ID, B.T2Name, B.Live
FROM @Tier1 AS A LEFT OUTER JOIN
@Tier2 AS B ON A.T1ID = B.T1IDFK
--WHERE A.Live = 1 AND B.Live = 1
ORDER BY A.ButtonLoc, A.T1Name, B.ButtonLoc, B.T2Name
April 27, 2010 at 2:18 pm
If you need the three conditions to be true, why are you coding left joins in stead of inner joins ??
If you only need to validate B and C if they have been joined, add the extra coalescy or isnull in your tests.
You need to validate b.live and c.live in case these columns are nullable in the originating table !
On that basis code your B.live = 1 (and c.live = 1) on the join part ! of the query
e.g.
Where A.live = 1 and isnull(B.Live, 1 ) = 1 and isnull(C.Live, 1 ) = 1
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 27, 2010 at 3:40 pm
Is this what you are looking for?
DECLARE @tier1 TABLE
(
T1ID INT,
T1Name NVARCHAR (255),
ButtonLoc INT,
live INT
)
INSERT INTO @tier1 (T1ID,T1Name,ButtonLoc,live)
VALUES (1,'Home',5,1);
INSERT INTO @tier1 (T1ID,T1Name,ButtonLoc,live)
VALUES (2,'About',10,1);
INSERT INTO @tier1 (T1ID,T1Name,ButtonLoc,live)
VALUES (3,'Regions',15,1);
DECLARE @tier2 TABLE
(
T2ID INT,
T1IDFK INT,
T2Name NVARCHAR (255),
ButtonLoc INT,
live INT
)
INSERT INTO @tier2 (T2ID,T1IDFK, T2Name,ButtonLoc,live)
VALUES (49,2,'About Us',5,1);
INSERT INTO @tier2 (T2ID,T1IDFK, T2Name,ButtonLoc,live)
VALUES (2,2,'Staff',10,1);
INSERT INTO @tier2 (T2ID,T1IDFK, T2Name,ButtonLoc,live)
VALUES (3,2,'Contact US',15,1);
INSERT INTO @tier2 (T2ID,T1IDFK, T2Name,ButtonLoc,live)
VALUES (3,2,'Navigation Tips',15,0);
SELECT A.T1Name, A.T1ID, A.live, B.T2ID, B.T2Name, B.live
FROM @tier1 AS A LEFT OUTER JOIN
@tier2 AS B ON A.T1ID = B.T1IDFK AND A.live = 1 AND B.live = 1
ORDER BY A.ButtonLoc, A.T1Name, B.ButtonLoc, B.T2Name
April 27, 2010 at 5:50 pm
Both of the suggestions work.
Which should I use?
April 28, 2010 at 12:54 am
I would prefer an inner join, but you'll have to figure out what kind of result set you actually need to determine if you need a left join.
Off course, if you have exact results with simular queries, choose the one that consumes less.
use "set statistics io, time on" to get feedback from the engine regarding the efforts it had to do to get to your result set.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply