CODEWITHSUNDEEP
sqlsql-server
SpindriftSeltzer
SpindriftSeltzer

Reputation: 321

Combine individual SQL Server queries into 1 query

I have three SQL Server queries that I'm attempting to combine into one query. I'm new to this level of SQL but these are the three queries I have

SELECT detail.map_id
, parcel.dsc_cd
, CASE WHEN ISNULL(parcel.dsc_cd, '') <> ''
       THEN (select dscr from lu_dsc where cd = parcel.dsc_cd)
       ELSE ''
  END AS Name
, parcel.nh_cd
, parcel.strap
, parcel.dor_cd
, detail.sub
, detail.section
, detail.township
, detail.range
, detail.acreage
, detail.sqft
FROM parcel
     INNER JOIN detail ON parcel.strap = detail.strap
WHERE parcel.status_cd = 'A'
AND (detail.map_id NOT IN (null,'','A','B','BE','BW','C','L','LL','MX','O','P','U')
    OR parcel.nh_cd IN (NULL,'0.00')
    OR parcel.dor_cd IN (NULL,'')
    OR detail.sub IN (NULL,'')
    OR detail.section IN (NULL,'')
    OR detail.township IN (NULL,'')
    OR detail.range IN (NULL,'')
    OR detail.acreage < 0
    OR detail.sqft < 0)
AND parcel.dor_cd NOT IN ('POSS', 'ALN')

SELECT detail.map_id MAP_ID
, parcel.nh_cd NBHD
, parcel.dor_cd DOR_CD
, detail.strap ACCOUNTNO
, detail.mkt_ar_2 LAND_MRKT
, detail.mkt_ar_1 ECON_AREA
FROM detail
     INNER JOIN parcel ON detail.strap = parcel.strap
WHERE parcel.status_cd = 'A'
      AND detail.mkt_ar_1 IN (NULL,'0')

SELECT TOP 100000 detail.map_id, parcel.strap, parcel.dor_cd, parcel.status_cd
FROM parcel INNER JOIN detail ON parcel.strap = detail.strap
WHERE (parcel.status_cd = 'A ')
AND NOT EXISTS (select 1 from legal_ln where legal_ln.strap = parcel.strap)

This is what I have so far. It runs without any errors but it's not pulling the correction information. I know that the query that looks for detail.mkt_ar_1 should have some results.

SELECT detail.map_id
, CASE WHEN ISNULL(parcel.dsc_cd, '') <> ''
       THEN (select dscr from lu_dsc where cd = parcel.dsc_cd)
       ELSE ''
  END AS Name
, parcel.nh_cd
, parcel.strap
, parcel.dor_cd
, detail.mkt_ar_1
, detail.sub
, detail.section
, detail.township
, detail.range
, detail.acreage
, detail.sqft
FROM parcel
     INNER JOIN detail ON parcel.strap = detail.strap
WHERE parcel.status_cd = 'A'
AND ((detail.map_id NOT IN (null,'','A','B','BE','BW','C','L','LL','MX','O','P','U')
    OR parcel.nh_cd IN (NULL,'0.00')
    OR parcel.dor_cd IN (NULL,'')
    OR detail.sub IN (NULL,'')
    OR detail.section IN (NULL,'')
    OR detail.township IN (NULL,'')
    OR detail.range IN (NULL,'')
    OR detail.acreage < 0
    OR detail.sqft < 0
    OR detail.mkt_ar_1 IN (NULL,'0'))
AND parcel.dor_cd NOT IN ('POSS', 'ALN'))
AND NOT EXISTS (select 1 from legal_ln where legal_ln.strap = parcel.strap)

How do I write this in such a way that it will run each query? Any advice regarding formatting would also be much appreciated.

Upvotes: 0

Views: 49

Answers (1)

MatBailie
MatBailie

Reputation: 86706

Combining the WHERE clauses...

WHERE
  (
        parcel.status_cd = 'A'
    AND parcel.dor_cd NOT IN ('POSS', 'ALN')
    AND
    (
          detail.map_id NOT IN (null,'','A','B','BE','BW','C','L','LL','MX','O','P','U')
      OR  parcel.nh_cd IN (NULL,'0.00')
      OR  parcel.dor_cd IN (NULL,'')
      OR  detail.sub IN (NULL,'')
      OR  detail.section IN (NULL,'')
      OR  detail.township IN (NULL,'')
      OR  detail.range IN (NULL,'')
      OR  detail.acreage < 0
      OR  detail.sqft < 0
    )
  )

OR

  (
        parcel.status_cd = 'A'
    AND detail.mkt_ar_1 IN (NULL,'0')
  )

OR

  (
        parcel.status_cd = 'A '
    AND NOT EXISTS (select 1 from legal_ln where legal_ln.strap = parcel.strap)
  )

That said, I don't think you're getting the behaviour you think with the NULLs, for example...

'x' NOT IN (NULL, 'y', 'z')  => Does NOT return TRUE, because of the NULL
   NULL IN (NULL, '')        => Does NOT return TRUE, because NULL never equals anything, even another NULL

Upvotes: 1

Related Questions