SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Combine 3 SQL queries


Combine 3 SQL queries

Author
Message
Mando_eg
Mando_eg
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 58
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
Thom A
Thom A
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45603 Visits: 15467
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Mando_eg
Mando_eg
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 58
using union will make them in the same column which is not correct in my case
Thom A
Thom A
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45603 Visits: 15467
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Mando_eg
Mando_eg
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 58
I'm aiming to have a table with 6 columns
SuggestionID - NewSuggestions - ComplainID - NewComplains - KidID - Name

my problem here I'm getting the data from 3 different tables which have no relationship between each other plus the number of rows maybe different
for example you may get data for
___________________________________________________________________
SuggestionID | NewSuggestions | ComplainID | NewComplains | KidID | Name
3 | sugSample | 4 | CompSampl | 20 | Hans
9 | sugSample2 | | | 21 | Sandra
----------------------------------------------------------------------------------------------------------------------
I know it might cause duplicates but this will not be a problem
pietlinden
pietlinden
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31598 Visits: 15129
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
Thom A
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45603 Visits: 15467
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Mando_eg
Mando_eg
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 58
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
Mando_eg
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 58
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
sgmunson
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43114 Visits: 5422
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)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search