Multiple records on query

I have the following query.  There should be a record for each value  in the  sn.code portion of the where clause.   I don't want 7 separate quires but I do need the seven records.
      
SELECT *
       FROM SpecialNeedsXREF snx
       JOIN SpecialNeeds sn ON snx.SpecialNeedID = sn.SpecialNeedID
      where snx.ClientID = '633985' and sn.Code in ('1','2','3','4','5','6','7')

Thanks
TimSweet220Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
And yur question is...what?

:)
0
Kevin CrossChief Technology OfficerCommented:
Are you saying there is not always a matching row in SpecialNeedsXREF? Additionally, are you guaranteed that there will be all 7 rows in the SpecialNeeds table?

To fix the former issue, you can use a LEFT OUTER JOIN, changing your query like so:
SELECT * /* columns you want to see here */
FROM SpecialNeeds sn 
LEFT OUTER JOIN SpecialNeedsXREF snx
   ON snx.SpecialNeedID = sn.SpecialNeedID AND snx.ClientID = '633985'
WHERE sn.Code in ('1','2','3','4','5','6','7')
;

Open in new window


For the latter issue, you would have to use a table of numbers or at least one that has 1-7, then LEFT OUTER JOIN the other two to it in a similar fashion.
0
TimSweet220Author Commented:
There may not be 7 records but most will result in 7 records.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TimSweet220Author Commented:
Sorry...I need it to show any record with the sn.code values and there may not be a record for each of those values.
0
Kevin CrossChief Technology OfficerCommented:
Then you will have to go with option #2 if I understand you correctly. You just need to build a table of numbers ... depending on your MS SQL version, you can do this a number of ways. Easiest is if you have a table of numbers, but short of that you can do a little typing :)

SELECT *
FROM (
   SELECT 1 AS Code UNION SELECT 2 UNION
   SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
   SELECT 6 UNION SELECT 7
) n
LEFT OUTER JOIN (
   SpecialNeeds sn 
   JOIN SpecialNeedsXREF snx ON snx.SpecialNeedID = sn.SpecialNeedID 
      AND snx.ClientID = '633985'
) ON sn.Code = n.Code
;

Open in new window


Hope that helps!
0
Kevin CrossChief Technology OfficerCommented:
Instead of using sn.Code, use n.Code where ever you are depending on all 7 values. If you need further explanation, please ask.
0
TimSweet220Author Commented:
Actually that didn't work it gave me 49 records - 7 for each sn.code value
0
TimSweet220Author Commented:
SELECT *
FROM (
   SELECT 1 AS Code UNION SELECT 2 UNION
   SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
   SELECT 6 UNION SELECT 7
) n
LEFT OUTER JOIN (
   SpecialNeeds sn
   JOIN SpecialNeedsXREF snx ON snx.SpecialNeedID = sn.SpecialNeedID
      AND snx.ClientID = '633985'
) ON sn.Code = sn.Code
order by sn.code
0
Kevin CrossChief Technology OfficerCommented:
Which one did? Please post the actual query you tried. It would be best also if you posted a sample of the data in both tables and your expectation of what the results are. Respectfully, with a JOIN on sn.Code = n.Code you should NOT get additional rows if you only have one row in each of the other tables per sn.Code. At least based on how I am reading your comments like "there may not be a record for each of those values" coupled with "There may not be 7 records but most will result in 7 records."

If there are duplicate matches at any point, then you will need to aggregate the data or add filters that select a unique row from the possible matches.

Anyway, the data example and query you are using should help clarify things.
0
Kevin CrossChief Technology OfficerCommented:
Hmm. We cross-posted, sorry.

SELECT *
FROM (
   SELECT 1 AS Code UNION SELECT 2 UNION
   SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
   SELECT 6 UNION SELECT 7
) n
LEFT OUTER JOIN (
   SpecialNeeds sn
   JOIN SpecialNeedsXREF snx ON snx.SpecialNeedID = sn.SpecialNeedID
      AND snx.ClientID = '633985'
) ON sn.Code = sn.Code
order by sn.code

Please look at my example and follow-up comment again. Those should probably be n.Code.
0
Kevin CrossChief Technology OfficerCommented:
*Those = the bolded elements of the query. Change the two sn.code instances that I bolded to n.code.
0
Kevin CrossChief Technology OfficerCommented:
And if you are getting 49 or 7x7, then I would double check that http:#36971333 really did not work for you unless it just so happens that this time there were all 7, but may not be the case in the future.
0
Andrei FomitchevCommented:
Results:

ClientId      SpecialNeedId
                |       Name      Code      
                |       |                |       Note
633984      1      Name1      1      Note 1 1
633984      1      Name1      2      NULL
633984      1      Name1      3      Note 1 3
633984      1      Name1      4      Note 1 4
633984      1      Name1      5      NULL
633984      1      Name1      6      NULL
633984      1      Name1      7      Note 1 7
633985      2      Name2      1      NULL
633985      2      Name2      2      NULL
633985      2      Name2      3      Note 1 1
633985      2      Name2      4      NULL
633985      2      Name2      5      NULL
633985      2      Name2      6      NULL
633985      2      Name2      7      Note 1 1

Query:

;WITH cte (No) AS (
	SELECT 1
	UNION ALL
	SELECT No+1 FROM cte
	WHERE No < 7
)
SELECT snx.ClientId, snx.SpecialNeedId, snx.Name, cte.No AS [Code], sn.Note
FROM SpecialNeedsXREF snx
CROSS JOIN cte
LEFT JOIN SpecialNeeds sn ON sn.Code = cte.No AND sn.SpecialNeedID = snx.SpecialNeedID
ORDER BY ClientId,SpecialNeedId,No
GO

Open in new window

Test Data:

CREATE TABLE SpecialNeedsXREF(SpecialNeedID int PRIMARY KEY, Name VarChar(50), ClientId int)
CREATE TABLE SpecialNeeds(Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, SpecialNeedID int, Code int, Note VarChar(50))
GO
INSERT INTO SpecialNeedsXREF(SpecialNeedID, Name, ClientId) VALUES
(1,'Name1',633984),
(2,'Name2',633985)
INSERT INTO SpecialNeeds(SpecialNeedID, Code, Note) VALUES
(1,1,'Note 1 1'),
(1,3,'Note 1 3'),
(1,4,'Note 1 4'),
(1,7,'Note 1 7'),
(2,3,'Note 1 1'),
(2,7,'Note 1 1')
GO


CREATE TABLE SpecialNeedsXREF(SpecialNeedID int PRIMARY KEY, Name VarChar(50), ClientId int)
CREATE TABLE SpecialNeeds(Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, SpecialNeedID int, Code int, Note VarChar(50))
GO
INSERT INTO SpecialNeedsXREF(SpecialNeedID, Name, ClientId) VALUES
(1,'Name1',633984),
(2,'Name2',633985)
INSERT INTO SpecialNeeds(SpecialNeedID, Code, Note) VALUES
(1,1,'Note 1 1'),
(1,3,'Note 1 3'),
(1,4,'Note 1 4'),
(1,7,'Note 1 7'),
(2,3,'Note 1 1'),
(2,7,'Note 1 1')
GO
;WITH cte (No) AS (
	SELECT 1
	UNION ALL
	SELECT No+1 FROM cte
	WHERE No < 7
)
SELECT snx.ClientId, snx.SpecialNeedId, snx.Name, sn.Code, sn.Note
FROM SpecialNeedsXREF snx
CROSS JOIN cte
LEFT JOIN SpecialNeeds sn ON sn.Code = cte.No AND sn.SpecialNeedID = snx.SpecialNeedID
ORDER BY ClientId,SpecialNeedId,No
GO

Open in new window

0
TimSweet220Author Commented:
Sorry, but I can't get that to work.
0
Kevin CrossChief Technology OfficerCommented:
Can you please be more specific please? I am unsure which of us you are referring to and what query.
0
Kevin CrossChief Technology OfficerCommented:
Did you make the corrections to your type-o's in the query I gave you? You had typed sn.Code twice when it should have been sn.Code = n.Code. Makes a big difference as you saw as one results in a Cartesian Product and the other a JOIN.
0
TimSweet220Author Commented:
I'm finally getting back to this. Will provide more shortly.
0
TimSweet220Author Commented:

Running this code:

SELECT *
FROM (
   SELECT 1 AS Code UNION SELECT 2 UNION
   SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
   SELECT 6 UNION SELECT 7
) n
LEFT OUTER JOIN (
   SpecialNeeds sn
   JOIN SpecialNeedsXREF snx ON snx.SpecialNeedID = sn.SpecialNeedID
      AND snx.ClientID = '633985'
) ON sn.Code = n.Code
order by n.code

I get this:
Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'INT' to a column of data type int.
0
TimSweet220Author Commented:
Running this code:

SELECT *
FROM (
   SELECT 1 AS Code UNION SELECT 2 UNION
   SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
   SELECT 6 UNION SELECT 7
) n
LEFT OUTER JOIN (
   SpecialNeeds sn
   JOIN SpecialNeedsXREF snx ON snx.SpecialNeedID = sn.SpecialNeedID
      AND snx.ClientID = '633985'
) ON sn.Code = sn.Code
--order by sn.code

Gives me 49 Records
0
TimSweet220Author Commented:
Problem is the field Code is varchar the specialneedsId is INT
0
Kevin CrossChief Technology OfficerCommented:
Run it like this, then:
SELECT *
FROM (
  SELECT '1' AS Code UNION SELECT '2' UNION
   SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION
   SELECT '6' UNION SELECT '7'

) n
LEFT OUTER JOIN (
   SpecialNeeds sn
   JOIN SpecialNeedsXREF snx ON snx.SpecialNeedID = sn.SpecialNeedID
      AND snx.ClientID = '633985'
) ON sn.Code = n.Code
order by n.code
;

Kevin
0
TimSweet220Author Commented:
Kevin,

Runs with out error but pulls back no data
Code      SpecialNeedID      Code      Description      ViewOrder      ClientID      SpecialNeedID      Selected      SpecialNeedsText
1      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL
2      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL
3      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL
4      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL
5      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL
6      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL
7      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL
0
Kevin CrossChief Technology OfficerCommented:
Is it possible, you have not rows matching the criteria?

What did you get when you ran this query?
SELECT * /* columns you want to see here */
FROM SpecialNeeds sn 
LEFT OUTER JOIN SpecialNeedsXREF snx
   ON snx.SpecialNeedID = sn.SpecialNeedID AND snx.ClientID = '633985'
WHERE sn.Code in ('1','2','3','4','5','6','7')
;

Open in new window


Just for comparative purposes.
0
TimSweet220Author Commented:
I should be getting back data

select * from SpecialNeedsXREF
where ClientID = '633985'

ClientID      SpecialNeedID      Selected      SpecialNeedsText
633985      1      0      eng
633985      2      0      NULL
633985      3      0      NULL
633985      4      0      NULL
633985      5      0      NULL
633985      6      0      NULL
633985      7      0      NULL
0
Kevin CrossChief Technology OfficerCommented:
Are you sure you need to JOIN to SpecialNeeds table to get the Code? It appears the 1-7 is the SpecialNeedID itself. Am I reading the above correctly? In other words, you started the question with a JOIN to another table, but then show results of a singular SELECT. Was the original query you posted returning any results?

Let's do this the easy way. Please post sample data from both tables involved with data structure for each along with relationship. Then show what the expected result is. We can build the query from there.
0
TimSweet220Author Commented:
 SpecialNeeds is basically a ref tables

SpecialNeedID      Code      Description      ViewOrder
1                       INT                Interpreter      1
2                      MOB                Assistance with Mobility      3
3                       VIS               Visual Impairment      5
4                      HEA               Hearing Impairement      7
5                      CUL                Cultural Uniqueness      9
6                       CHI               Child Supervision      11
7                       SMI                SMI Cognitive Impairment      13


I just trying to get a data set that include the CODE so that it can be displayed in the,

when I pulls this type of data (by individual) (specialNeedID = Code)

ClientID      SpecialNeedID      Selected      SpecialNeedsText
633985               1                    0           eng
633985               2                   0                   NULL
633985               3                   0                   NULL
633985               4                   0                   NULL
633985               5                   0                   NULL
633985                   6                   0                   NULL
633985             7                   0                   NULL

0
Kevin CrossChief Technology OfficerCommented:
Okay, then your WHERE clause was incorrect to begin with. You may not need to join in the table of numbers.

Try:
SELECT snx.*, sn.Code
FROM SpecialNeedsXREF snx 
JOIN SpecialNeeds sn ON snx.SpecialNeedID = sn.SpecialNeedID 
WHERE snx.ClientID = '633985' 
AND snx.SpecialNeedID BETWEN 1 AND 7
;

Open in new window


If SpecialNeedID is INT, then filter it as an INT to avoid unnecessary implicit conversion. If it is VarChar, then just put back that comparison with '1', '2', etc. The important piece is that those values are in SpecialNeedID column and NOT Code.

If you do have the challenge where a SpecialNeedID may be missing, then the other solution would look like this:
SELECT n.ID, snx.*, sn.Code
FROM (
  SELECT '1' AS ID UNION SELECT '2' UNION
   SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION
   SELECT '6' UNION SELECT '7'
) n
LEFT OUTER JOIN (
   SpecialNeeds sn
   JOIN SpecialNeedsXREF snx ON snx.SpecialNeedID = sn.SpecialNeedID
      AND snx.ClientID = '633985'
) ON snx.SpecialNeedID = n.ID
ORDER BY n.ID
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TimSweet220Author Commented:
Worked perfectly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.