Combine 3 SQL queries

Question

Post reply

Combine 3 SQL queries

Mando_eg

SSChasing Mays

Points: 648
More actions
November 19, 2017 at 5:11 am

#341387

Hello,
I want the combine the following queries
Select Complains.[Subject] as NewComplains,Complains.ID AS ComplainID from Complains where Complains.ReadStatus = 'false' Select Suggestions.[Subject] as NewSuggestions,Suggestions.ID AS SuggestionID from Suggestions where Suggestions.ReadStatus = 'false' Select KidID,Name from KidsData where KidsData.KidID = (Select KidID from Payments where EndDate < GETDATE())
NOTICE
- There is no relationship between the 3 tables
- No problem if there are duplicates I will handle it in my code
- The tables may have different numbers of rows

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

Without any DDL, and little explanation, this a bit of a guess, however, have a look at UNION and UNION ALL.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Mando_eg SSChasing Mays Points: 648 More actions · Answer 2

using union will make them in the same column which is not correct in my case

Thom A SSC Guru Points: 99133 More actions · Answer 3

Then what is you're aim? You should supply DDL, sample data and expected outputs for T-SQL questions, could you do so please? You can see how to by the link in my signature.

Thanks.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Mando_eg SSChasing Mays Points: 648 More actions · Answer 4

I'm aiming to have a table with 6 columns
SuggestionID - NewSuggestions - ComplainID - NewComplains - KidID - Name

pietlinden SSC Guru Points: 63509 More actions · Answer 5

Sounds like you need something like
(Kid LEFT JOIN Suggestions) LEFT JOIN Complaints

But then you'd need KidID in Suggestions AND in Complaints. Otherwise, how do you know which "kid" record is related to the Suggestion/Complaint record?

Thom A SSC Guru Points: 99133 More actions · Answer 6

You say there is "no relationship", however, what defines that KidID 20 appears on the same line as ComplainID 4 and SuggestionID 3? Is it just "random"?

This is another total guess, without DDL, sample data and Expected results, and is completely untested but..:

WITH Crn AS (
  SELECT C.[Subject] AS NewComplains, C.ID AS ComplainID,
    ROW_NUMBER() OVER (ORDER BY NEWID()) AS RN --Change your ORDER BY to be whatever you like/need
   FROM Complains C
   WHERE C.ReadStatus = 'false'),
Srn AS (
   SELECT S.[Subject] AS NewSuggestions, S.ID AS SuggestionID,
     ROW_NUMBER() OVER (ORDER BY NEWID()) AS RN --Change your ORDER BY to be whatever you like/need
   FROM Suggestions S
   WHERE S.ReadStatus = 'false'),
KDrn AS (
   SELECT KD.KidID, KD.Name,
     ROW_NUMBER() OVER (ORDER BY NEWID()) AS RN --Change your ORDER BY to be whatever you like/need
   FROM KidsData KD
   WHERE KD.KidID = (Select KidID from Payments where EndDate < GETDATE())) --This will only return one row?? Seems unlikely
SELECT Crn.NewComplains, Crn.ComplainID,
   Srn.NewSuggestions, Srn.SuggestionID,
    KDrn.KidID, KDrn.Name
FROM Crn
   FULL OUTER JOIN Srn ON Crn.RN = Srn.RN
  FULL OUTER JOIN KDrn ON Crn.RN = KDrn.RN OR Srn.RN = KDrn.RN; --Because C might be NULL  

If there any errors in the syntax, you'll need to resolve those.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Mando_eg SSChasing Mays Points: 648 More actions · Answer 7

Thank you Thom A,
thats exactly what I need to do

Thank you all for replys and help
--------------------
the idea was to have a form to view a summary of notifications based on user choices, So the notifications could be from different tables which not related to each other,
However, Thom A's code is exactly what I need to do

Regards

Mando_eg SSChasing Mays Points: 648 More actions · Answer 8

Hello Thom A,
it seems there is something wrong with the last query, if there are more than 1 record it return the same error again

any suggestion ?

sgmunson SSC Guru Points: 110640 More actions · Answer 9

Mando_eg - Monday, December 25, 2017 3:57 AM
Hello Thom A,
it seems there is something wrong with the last query, if there are more than 1 record it return the same error again
any suggestion ?

You haven't provided any details on what error you are getting, nor whether or not you made any modifications to Thom A's code to fit your situation. Those are essential elements when troubleshooting.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

kevaburg SSCoach Points: 18131 More actions · Answer 10

I am really struggling to understand what the aim is. All three tables produce three completely different answers to three completely different questions. To that end presenting the data in the format you are suggesting is completely misleading and interpretion at best, extremely difficult.

If you are looking for a summary of notifications for each of the three areas represented by your queries, then create a parameterised report using the three tables each presented separately.

At least that is what I would do.....

Thom A SSC Guru Points: 99133 More actions · Answer 11

Like the others said, if a different scenario is providing a "wrong" result, or error message, we need to know what the "wrong" result is and why, or what the error message is.

I'll quote my original reply again here, just remember to post the data for your new scenario

Thom A - Sunday, November 19, 2017 5:47 AM
Then what is you're aim? You should supply DDL, sample data and expected outputs for T-SQL questions, could you do so please? You can see how to by the link in my signature.
Thanks.

Please do not post it like you did before (seen below); make it consumable.

Mando_eg - Sunday, November 19, 2017 6:21 AM
___________________________________________________________________
SuggestionID | NewSuggestions | ComplainID | NewComplains | KidID | Name
3 | sugSample | 4 | CompSampl | 20 | Hans
9 | sugSample2 | | | 21 | Sandra
----------------------------------------------------------------------------------------------------------------------

Thanks

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Matt Simmons SSCrazy Points: 2846 More actions · Answer 12

Here is what you need, I think. Union All works but in order to get the results you want you need to add some NULL columns. It's a weird work around, but I think this gets you what you need.

Select Complains.[Subject] AS NewComplains,Complains.ID AS ComplainID, NULL AS NewSuggestions, NULL AS SuggestionID, NULL AS Name from Complains where Complains.ReadStatus = 'false'
UNION ALL
Select NULL AS NewComplains, NULL AS ComplainID, Suggestions.[Subject] as NewSuggestions,Suggestions.ID AS SuggestionID, NULL AS Name from Suggestions where Suggestions.ReadStatus = 'false'
UNION ALL
Select NULL AS NewComplains, NULL AS ComplainID,NULL AS NewSuggestions, NULL AS SuggestionID, KidID,Name from KidsData where KidsData.KidID = (Select KidID from Payments where EndDate < GETDATE())

Regards,
Matt

Mando_eg SSChasing Mays Points: 648 More actions · Answer 13

Well, Let me explain the scenario of this procedure
I have 3 tables
Suggestions, Complains, Payments
I'm getting a notification of New or Unread of Suggestion and Complains and I get a notification about the expired period from Payment
for example if the payment covers from 1-Dec-2017 I should get a notification about it on 1-Jan-2018
so instead of doing 3 queries I want combine them all to do only 1 query
the problem now is when there are many different results of each table I get that error
for example
I have 0 new complains , 1 new suggestion, 2 expired payments
I get the below error
Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 14

Mando_eg - Sunday, January 7, 2018 12:20 AM
Well, Let me explain the scenario of this procedure
I have 3 tables
Suggestions, Complains, Payments
I'm getting a notification of New or Unread of Suggestion and Complains and I get a notification about the expired period from Payment
for example if the payment covers from 1-Dec-2017 I should get a notification about it on 1-Jan-2018
so instead of doing 3 queries I want combine them all to do only 1 query
the problem now is when there are many different results of each table I get that error
for example
I have 0 new complains , 1 new suggestion, 2 expired payments
I get the below error
Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Ah, eventually we get an error message. Your error is right in your first post. You can't do this

Select KidID,Name from KidsData where KidsData.KidID = (Select KidID from Payments where EndDate < GETDATE())
if there are potentially more than one rows coming back from the Payments table. Which row would it pick? What you should rather do is
SELECT KD.KidID, KD.Name FROM KidsData KD JOIN Payments P ON KD.KidID = P.KidID ON KD.KidID = P.KidID WHERE P.EndDate < GETDATE()

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]