Using Where or And in

[nc297]

My boss said this is incorrect. I shouldn't have to join the natdocfile twice in both queries only one time. If I don't join it at all then I don't get all of the records in the natdocfile.

She also said I need a where clause instead of using the word "and". Here's some info so you can test as well to see which results are correct.

First Table and Insert statement

CREATE TABLE [dbo].[T16pendall](
[COSSN] [char](6) NOT NULL,
[FLG_CDT] [datetime] NULL,
[AGED_ALIEN_RSW] [char](1) NOT NULL,
[MFT_POSN1_CD] [char](1) NOT NULL,
[FO] [varchar](4) NOT NULL
) ON [PRIMARY]



insert into T16pendall
select '268763', '10/6/2010', ' ', 'D', '389' union all
select '337629', '10/21/2010', ' ', 'D', 'A00' union all
select '240312', '10/28/2010', ' ', 'D', '330' union all
select '406339', '10/4/2010', ' ', 'D', '442' union all
select '279700', '11/1/2010', ' ', 'D', '387' union all
select '680031', '10/29/2010', ' ', 'D', 'A00' union all
select '298582', '10/26/2010', ' ', 'D', '387' union all
select '074506', '9/23/2010', ' ', 'D', '329' union all
select '610128', '10/6/2010', ' ', 'A', '893' union all
select '427337', '6/30/2010', ' ', 'D', 'B14' union all
select '553084', '10/15/2010', ' ', 'D', '913' union all
select '587962', '8/30/2010', ' ', 'D', '100' union all
select '746855', '9/10/2010', ' ', 'D', 'A38' union all
select '429194', '9/13/2010', ' ', 'D', 'A00' union all
select '424431', '9/14/2010', ' ', 'D', 'A00'

Second Table and Insert statement

CREATE TABLE [dbo].[T16pendmvt](
[COSSN] [char](6) NOT NULL,
[MVT_TYP] [char](1) NOT NULL,
[MVT_LOC] [char](3) NOT NULL,
[MVT_DEST] [varchar](3) NOT NULL,
[MVT_CDT] [datetime] NULL
) ON [PRIMARY]

insert into T16pendmvt
select '074506', 'T', '329', 'S36', '9/23/2009' union all

select '610128', 'R', 'S15', ' ', '10/6/2010' union all

select '427337', 'R', 'S27', ' ', '7/1/2010' union all

select '553084', 'R', 'V64', ' ', '10/26/2010' union all

select '999962', 'T', 'SS2', 'R42', '2/3/2003' union all

select '452262', 'R', 'VS2', ' ', '1/22/2004' union all

select '458962', 'R', 'V36', ' ', '5/5/2004' union all

select '458962', 'R', 'V25', '858', '6/8/2004' union all

select '458962', 'T', 'S72', 'S24', '7/19/2004' union all

select '458962', 'R', '004', ' ', '4/8/2010'

Last table and insert statement

CREATE TABLE [dbo].[natdocfile](
[doc] [varchar](3) NOT NULL
) ON [PRIMARY]


insert into natdocfile
select '001' union all
select 'A00' union all
select '389' union all
select 'A38' union all
select 'B14' union all
select '913' union all
select '009' union all
select '00K' union all
select '00N' union all
select '00U'

After you've created the tables enter this into SQL Query and run:

SELECT a.doc, pendinfo1, pendinfo2

--INTO #temppd

FROM

(select n.doc,Isnull(COUNT(t.cossn), 0)AS PendinFO1
FROM t16pendall t
right join natdocfile n on n.doc=t.fo
and (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cossn not in(select cossn from t16pendmvt)
GROUP BY n.doc)a

join

(SELECT n.doc, Isnull(COUNT(m.cossn), 0) AS pendinfo2
FROM t16pendmvt m
JOIN t16pendall t
ON m.cossn = t.cossn
right join natdocfile n on n.doc=t.fo
and ( mvt_typ = 'R' ) AND not (mvt_loc LIKE 'R%' or mvt_loc LIKE 'S%' OR mvt_loc LIKE 'V%' )
GROUP BY n.doc)b
on a.doc = b.doc




SELECT a.doc, pendinfo1, pendinfo2

FROM

(select n.doc,Isnull(COUNT(t.cossn), 0)AS PendinFO1
FROM t16pendall t
right join natdocfile n on n.doc=t.fo
where (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cossn not in(select cossn from t16pendmvt)
GROUP BY n.doc)a

join

(SELECT n.doc, Isnull(COUNT(m.cossn), 0) AS pendinfo2
FROM t16pendmvt m
JOIN t16pendall t
ON m.cossn = t.cossn
right join natdocfile n on n.doc=t.fo
where ( mvt_typ = 'R' ) AND not (mvt_loc LIKE 'R%' or mvt_loc LIKE 'S%' OR mvt_loc LIKE 'V%' )
GROUP BY n.doc)b
on a.doc = b.doc

SELECT a.fo, pendinfo1, pendinfo2

--INTO #temppd

FROM

(select t.fo,Isnull(COUNT(t.cossn), 0)AS PendinFO1
FROM t16pendall t
--right join natdocfile n on n.doc=t.fo
where (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cossn not in(select cossn from t16pendmvt)
GROUP BY t.fo)a

join

(SELECT t.fo, Isnull(COUNT(m.cossn), 0) AS pendinfo2
FROM t16pendmvt m
JOIN t16pendall t
ON m.cossn = t.cossn
--right join natdocfile n on n.doc=t.fo
where ( mvt_typ = 'R' ) AND not (mvt_loc LIKE 'R%' or mvt_loc LIKE 'S%' OR mvt_loc LIKE 'V%' )
GROUP BY t.fo)b
on a.fo = b.fo

The first query gives me results but how I'm joining the natdocfile is not what she wants. Only wants it joined one time.

The second query I have the natdocfile listed but changed the "and" to "where" but nothing came back.

The last one I took out the natdocfile file and nothing comes back. Don't know which way to go.

 

[hopper44]

Try this:

SELECT n.doc, ISNULL(a.pendinfo1,0) AS pendinfo1, ISNULL(pendinfo2,0) AS pendinfo2
FROM natdocfile n
LEFT OUTER JOIN
(SELECT t.fo, ISNULL(COUNT(t.cossn), 0)AS pendinfo1
FROM t16pendall t
WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cossn not in (select cossn from t16pendmvt)
GROUP BY t.fo) a
ON n.doc = a.FO
LEFT OUTER JOIN
(SELECT t.fo, Isnull(COUNT(m.cossn), 0) AS pendinfo2
FROM t16pendmvt m inner join T16pendall t ON m.cossn = t.cossn
WHERE (mvt_typ = 'R' ) AND not (mvt_loc LIKE 'R%' or mvt_loc LIKE 'S%' OR mvt_loc LIKE 'V%' )
GROUP BY t.fo) b
on n.doc = b.fo

 

[nc297]

Thanks so much Hopper44 this works fine and exactly what I wanted! So I see to call the natdocfile only once put it as the first table.

 

[nc297]

Question...why do you use left outer join instead of just a left join?

 

[markros]

The OUTER keyword is optional. It's up to you to use/not use it.

PluralSight Learning Library

 

[nc297]

ok great thanks!