Solved

Specific SQL query

Posted on 2003-11-03
8
554 Views
Last Modified: 2008-03-10
I would like to have the SQL to perform a specific query that presently I am implementing by two separate queries and row by row processing.

Here are simplified versions of the tables (there are many other attributes not shown as they are not needed for the query)

Users
   UserId   int,
   Status    int,
   UserTypeId   int

Manufacturers
   ManuacturerId   int,
   UserId  int

UserNAICS
   UserId         int,
   NAICtype   int,
   NAICS         char(6)

The Users and Manufacturers tables are 1-1, and the Users and UserNAICS are 1-M. There are two types of users (mfg and rep), and two types of NAICS (product and industry).

I would like to query these tables for a given UserTypeId and NAICType to return all users that have a maximum NAICS length of 2 characters, plus all that have no entries in the UserNAICS table for the given NAICType.

The issues I am running in to have to do with the presence of NULLS. When I have tried to do it all in one query using LEFT joins, the testing for the length of the NAICS attribute causes the null rows to not appear.

So far, my solution is to query in two parts: one to just get the 2-charater NAICS, and a second query that returns mfgs that have no entries of either type (NULL in the NAICType column) and then process the table row by row to determine if the NAICType is missing.

Here are the queries I am presently using for UserTypeID of 2 and NAICSType of 2 and a sample of the data returned:

Note: the HAVING clause is needed because in addition to NAICS with actual length of 2, there are codes such as '31-33' which is interpreted as a range of two-character codes 31, 32, 33.

-----------------------------------------------
-- Query #1: To get those having NAICS length of 2
-----------------------------------------------
SELECT     M.ManufacturerId, MAX(LEN(N.NAICS) - CHARINDEX('-', N.NAICS)) AS MaxDigits
FROM         Manufacturers M LEFT OUTER JOIN
                      UserNAICS N INNER JOIN
                      Users U ON N.UserId = U.UserId ON M.UserId = U.UserId
WHERE     (U.UserTypeId = 2) AND (U.Status <> 'Inactive') AND (N.NaicType = 2)
GROUP BY M.ManufacturerId
HAVING      (MAX(LEN(N.NAICS) - CHARINDEX('-', N.NAICS)) = 2)
ORDER BY MaxDigits, M.ManufacturerId DESC

ManufacturerId       MaxDigits
      1849      2
      1846      2
      1840      2
      1833      2
 
-----------------------------------------------
-- Query #2: To get those having no NAICS (there is a null, or have to read
-- row by row to see that there is no row for that Mfg and that NAICSType:
-----------------------------------------------

SELECT     m.ManufacturerId, N.NaicType
FROM         Users u LEFT OUTER JOIN
                      UserNAICS N ON u.UserId = N.UserId INNER JOIN
                      Manufacturers m ON m.UserId = u.UserId
WHERE     (u.UserTypeId = 2) AND (u.Status <> 'Inactive')
GROUP BY m.ManufacturerId, N.NaicType
ORDER BY m.ManufacturerId DESC

ManufacturerId      NAICSType
      1854      <null>
      1853      1
      1852      1
      1851      1
      1850      1
      1849      2
      1849      1
      1848      <null>
      1847      1
      1846      1
      1846      2
      1844      1
      1844      2
      1843      1

The result I want takes all rows from Query #1, then adds all mfgs from Query #2 that do not have a row with the NAICType in question. In this example, I would end up with rows for the following list of ManufacturerId, because they either have 2-char NAICS, or NULL in the second query, or no rows in the second query with NAICSType of 2:

      1849
      1846
      1840
      1833
      1854
      1853
      1852
      1851
      1850
      1848
      1847
      1843

0
Comment
Question by:jasimon9
8 Comments
 
LVL 22

Expert Comment

by:CJ_S
Comment Utility

declare @users table(userid int identity(1,1), status varchar(10), usertypeid int)
declare @manufacturers table(userid int, manufacturerid int)
declare @UserNAICS table(userid int, NAICType int, NAICS char(6))

declare @userid int
insert into @users values('Active', 2)
set @userid = @@IDENTITY

insert into @manufacturers values(@userid, 1)

insert into @UserNAICS values(@userid, 2, 'ab')
insert into @UserNAICS values(@userid, 2, 'abcd')
insert into @UserNAICS values(@userid, 2, NULL)
insert into @UserNAICS values(@userid, 3, 'a')
insert into @UserNAICS values(@userid, 3, '12-33')


-- HERE STARTS THE MORE IMPORTANT STUFF FOR YOU --
declare @NAICSLENGTH int, @USERTYPEID int
set @USERTYPEID = 2
set @NAICSLENGTH = 2

SELECT             M.ManufacturerID,
FROM            @Users U
left join      @manufacturers M ON
            U.userid = M.userid
left join      @UserNAICS N ON
            U.userid = N.userid
AND            LEN(N.NAICS)=@NAICSLENGTH OR (CHARINDEX('-', NAICS)=(@NAICSLENGTH+1) AND (LEN(NAICS)-CHARINDEX('-', NAICS))=@NAICSLENGTH) OR N.NAICS IS NULL
WHERE            U.UserTypeID = @USERTYPEID
GROUP BY      M.ManufacturerID
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
If you specify your selection criteria on the tables in subqueries
which you join you'll probably end up with a better
query plan and the correct results...

with Naics
'-' +  convert(varchar(6),ltrim(rtrim(coalesce(naics,'')))) + '-'  Like '%-__-%'  

trim off any spaces...
change null to an empty string
convert to a varchar
append '-'  to both ends
then search for any 2 characters between '-''s
althought I now not sure if you wanted to ignore those cases
where naics was say '31-123'


ok slightly confused....

do you just want the cases where a naics code is 2 char  or the specifiy niactype?
or do you want see them...?


SELECT     M.ManufacturerId
FROM         Manufacturers M
inner Join ( Select * from Users
                    Where UserTypeId = 2 AND Status <> 'Inactive'
                ) as  U
on U.Userid = M.UserId
LEFT OUTER JOIN
                    (select UserId ,Naics
                From  UserNAICS
                 Where NaicType=2  or
                 '-' +  convert(varchar(6),ltrim(rtrim(coalesce(naics,'')))) + '-'  Like '%-__-%'        ) as  N
on U.Userid = N.Userid

0
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
Try this  (UNION :))

SELECT     m.ManufacturerId
FROM         Users u LEFT OUTER JOIN
                      UserNAICS N ON u.UserId = N.UserId INNER JOIN
                      Manufacturers m ON m.UserId = u.UserId
WHERE     (u.UserTypeId = 2) AND (u.Status <> 'Inactive')
GROUP BY m.ManufacturerId, N.NaicType
UNION
SELECT     M.ManufacturerId
FROM         Manufacturers M LEFT OUTER JOIN
                      UserNAICS N INNER JOIN
                      Users U ON N.UserId = U.UserId ON M.UserId = U.UserId
WHERE     (U.UserTypeId = 2) AND (U.Status <> 'Inactive') AND (N.NaicType = 2)
GROUP BY M.ManufacturerId
HAVING      (MAX(LEN(N.NAICS) - CHARINDEX('-', N.NAICS)) = 2)
ORDER BY 1


:)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:jasimon9
Comment Utility
Thank you all for your initial attempts. Unfortunately, none of these queries works. Perhaps a little tweak will get what I need. Here are the detailed results:

1. Query from CJ_S:

The query gives an error. Since I already have tables set up, to use what was presented with my tables I removed the "@" from the table names. That is, the original portion of the query presented as

declare @NAICSLENGTH int, @USERTYPEID int
set @USERTYPEID = 2
set @NAICSLENGTH = 2

SELECT           M.ManufacturerID,
FROM          @Users U
left join     @manufacturers M ON
          U.userid = M.userid
left join     @UserNAICS N ON
          U.userid = N.userid
AND          LEN(N.NAICS)=@NAICSLENGTH OR (CHARINDEX('-', NAICS)=(@NAICSLENGTH+1) AND (LEN(NAICS)-CHARINDEX('-', NAICS))=@NAICSLENGTH) OR N.NAICS IS NULL
WHERE          U.UserTypeID = @USERTYPEID
GROUP BY     M.ManufacturerID

I changed to

declare @NAICSLENGTH int, @USERTYPEID int
set @USERTYPEID = 2
set @NAICSLENGTH = 2

SELECT           M.ManufacturerID,
FROM          Users U
left join     manufacturers M ON
          U.userid = M.userid
left join     UserNAICS N ON
          U.userid = N.userid
AND          LEN(N.NAICS)=@NAICSLENGTH OR (CHARINDEX('-', NAICS)=(@NAICSLENGTH+1) AND (LEN(NAICS)-CHARINDEX('-', NAICS))=@NAICSLENGTH) OR N.NAICS IS NULL
WHERE          U.UserTypeID = @USERTYPEID
GROUP BY     M.ManufacturerID

Then I just used the portion you presented as "the important part." The query then gives the error "incorrect syntax near the keyword 'FROM'.  Perhaps I am not interpreting how this should be implemented. However, the code above does not work.

2. Query from Lowfatspread

You questioned about '31-33'. The precise anwser here is that there are certain NAICS codes in the column shown literally as '31-33', '44-45', and '48-49'. All the rest of the NAICS codes are from 2 to 6 digit numbers. I hope this clarifies this.

I added "ORDER BY M.ManufacturerId DESC" to make it easy to check, and also DISTINCT to list each mfg only once. However, the part about the "slightly confused" and the "like" clause with the underbars may be not literally what you intended.

In any case, the query runs. I have not checked it completely but it includes rows that it should not. For example, mfg 1844 appears. This mfg has NIACS codes of type 2 of 311, 4244, and 445. Since the query is attempting return mfgs having no codes of type 2 or only codes of length 2, this fails because all three codes are of length greater than 2.
 
3. Query from namasi_navaretnam

Adding DESC to the query, this query returns the exact same resutls as the adjusted SQL from Lowfatspread.

This leads me to believe that I may not have specified the problem correctly. In any case, mfg 1844 is also included, with type 2 NAICS of 311,4244, and 445.

I believe that the with the proper adjustment, someone will get it right.

To restate the problem: given a usertypeid (for example 2 - mfg) and a NAICType (for example 2 - product), list the distinct manufacturerids that either have no NAICS codes of that type, or only have NAICS codes of that type of length 2. The opposite way of saying this is that the complement of this set (that is, the ones we don't want) have NAICS codes of length greater than 2.

Perhaps something like

SELECT ...
WHERE manufacturerid NOT IN (SELECT where length of NAICS code > 2)

Actually this could be an easy way to do it, and I may try it myself. I will still give the points to anyone who gets it that way too.
0
 

Accepted Solution

by:
jasimon9 earned 0 total points
Comment Utility
Because of the thinking I was doing during my previous response to the first three submitters (none of whose proposals worked), I came up with an approach which in fact gives the correct results. Here is the SQL that works:

----------------------------------------------------
SELECT ManufacturerId
FROM Manufacturers M INNER JOIN Users U ON M.UserId = U.UserId
WHERE U.Status <> 'Inactive' AND ManufacturerId NOT IN
(SELECT     M.ManufacturerId
FROM         Manufacturers M LEFT OUTER JOIN
                      UserNAICS N INNER JOIN
                      Users U ON N.UserId = U.UserId ON M.UserId = U.UserId
WHERE     (U.UserTypeId = 2) AND (U.Status <> 'Inactive') AND (N.NaicType = 2)
GROUP BY M.ManufacturerId
HAVING      (MAX(LEN(N.NAICS) - CHARINDEX('-', N.NAICS)) > 2))
ORDER BY 1
-------------------------------------------------

The key to making this work was the realization that it would be easier to define the COMPLEMENT of the set I was looking for, and the nested select does that.

So at this point, I will be withdrawing the question, subject to further verification that it produces the correct results.
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
the like part with the underscores was intended to identify the two character
codes...
my failure was in missinterpreting the requirement
for row to either not have the specified type or the two char limitation...

the niac_type = 2 should be removed
in my example and  the following amendment done...

SELECT     M.ManufacturerId
FROM         Manufacturers M
inner Join ( Select * from Users
                    Where UserTypeId = 2 AND Status <> 'Inactive'
                ) as  U
on U.Userid = M.UserId
LEFT OUTER JOIN
                    (select UserId ,Naics
                From  UserNAICS
                 Where
                 '-' +  convert(varchar(6),ltrim(rtrim(coalesce(naics,'')))) + '-'  Like '%-__-%'        ) as  N
on U.Userid = N.Userid
Where N.userid is not null
or not exists (select userid from usernaics where NaicType=2  and userid = u.userid )




0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
execute a MS SQL script as a schedule SQL job 72 97
Choosing SSD drives for SQL Server 32 77
MS SQL Backup 24 69
Ranking Based On Value 3 28
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now