Gather 22 results windows into one.

I really would be questioning your approach at this stage. Why do you want to return so much data? My guiding principle when creating queries is to always return the least data possible to achieve a certain end. Also, you are using a CustomerId parameter but comparing to a ContactId in some of the conditions. Is this really what you wanted? If it is, you could extend my other answer to do what you want here but I advise strongly against it if it is a query that will be run frequently.

The KISS principle really does apply here, IMO.

Powershell scripting reason, Im using the:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;"
	
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandText = $SqlQuery
	$SqlCmd.Connection = $SqlConnection
	
	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	$SqlAdapter.SelectCommand = $SqlCmd
	
	$DataSet = New-Object System.Data.DataSet
	[void]$SqlAdapter.Fill($DataSet)
	
	$SqlConnection.Close()
	#-----------------------------------------
	
	return $DataSet.Tables[0].Rows

Select allOpen in new window

When I add 22 select in there, its failing and saying that Im overflow the $Dataset. Need to be one.

I am still going to stick to my guns a little here and say "Why?" What do you plan on doing with the data? Why could you not consider performing multiple queries to extract relevant data rather than just load your server with an unnecessarily complex query? Are you really going to process every row and column, no matter when it was created? As your database grows, it could well become a significant load on the server.

If you really want to go down this route, then you would do as I suggested in my answer to question https://www.experts-exchange.com/questions/29121715/SQL-select-query.html

Feel free to rewrite your query and post it back here if it gives you an error and I (or someone else) will take a look. But please consider server load and performance before writing such queries. There is certainly a better (by that I mean simpler, cleaner and more efficient) way than the route I see you taking with the limited information that I have available.

The last query was just a test theory , it was not complete, however if I use left join I got problem at:
Select * from History_Work where pxHistoryForReference in (Select InsKey from Work where ContactId= @SCustomerID)
I just don't know how to write that in after the left join... So i put the whole query here.

After I put this into $dataset, I will send this result in mail form. All are automatic processes.

Generally a query with a subquery like that can be rewritten to avoid a subquery but be equivalent. It would may not be very efficient at all though. In your case, the subqueries could be included in the where clause, from my cursory reading.

I am going to ask again about your naming - you have a column called ContactId but your parameter is @SCustomerId. To someone looking at that query it would raise questions as to why you are comparing a ContactId to a CustomerId (because they could be different entities). Naturally your database may be built that way, but it seems a little odd to me.

Edit: Removed example because it was not likely to help in this situation, sorry.

They build the system as this way... in some tables there are CustomerID, and some are ContactID, they are matched.

Yes. I guessed that but it is an inconsistency in naming none the less.  Hopefully my suggestion above will help you rewrite your query to remove the subqueries. There are other approaches too, but let's keep it straightforward.

This is what i done so far... this is not working at all tho...

	DECLARE 
		@SCustomerID varchar(30) = '56201710194';
	DECLARE
	    @SSN varchar(30) = '1123560344';
			
	Select * 
	from Data_Person mdb left join Data_Address mdas on mdas.CustomerID = mdb.CustomerID  
	left join Data_Email mdes on mdes.CustomerID = mdb.CustomerID left join Data_Employment 
	mdes2 on mdes2.CustomerID = mdes.CustomerID left join Data_Membership mdms on mdms.CustomerID = mdes.CustomerID
	left join Data_Telephone mdts on mdts.CustomerID = mdes.customerID left join Work mws on mws.ContactId = mdts.CustomerID
	left join Index_Interactions miis on miis.ContactId = mws.ContactId left join Data_MembershipHistory mdmsh on mdmsh.CustomerID = mdms.CustomerID
	left join Data_MembershipProgress mdmps on mdmps.CustomerID = mdts.CustomerID left join Data_WorkAttach mdwss left join History_Work mhws left join
	Index_ToDoList mits left join Data_CaseProcessingTime mdct left join data.pc_assign_workbasket paw left join data.pc_assign_worklist paw2 left join 
	data.pr_sys_locks psl left join data.pc_link_attachment pla left join Queue_ConnectCall mqcc left join Index_IncompleteCases miis2 left join Data_CustomerNotes mdcs

    WHERE mdb.CustomerID = @SCustomerID or miis2.ssn = @SSN or mdcs.ssn = @ssn or mdwss.pxHistoryForReference in (Select InsKey from Work where ContactId= @SCustomerID) or 
	mdwss.pxRefObjectKey in (Select InsKey from Work where ContactId= @SCustomerID) or
	mhws.pxHistoryForReference in (Select InsKey from Work where ContactId= @SCustomerID) or
	mits.pxInsIndexedKey in (Select InsKey from Work where ContactId= @SCustomerID) or
	mdct.pyID in (Select pyID from Work where ContactId= @SCustomerID) or
	paw.pxRefObjectKey in (Select InsKey from Work where ContactId= @SCustomerID) or
	paw2.pxRefObjectKey in (Select InsKey from Work where ContactId= @SCustomerID) or
	psl.InsKey in (Select InsKey from Work where ContactId= @SCustomerID) or
	pla.pxLinkedRefFrom in (Select InsKey from Work where ContactId= @SCustomerID) or
	mqcc.pyID in (Select pyID from Work where ContactId= @SCustomerID)

Select allOpen in new window

It would help if you specify the exact error you see when running the query. I would also reformat the query so it is a little easier to read. Do you have some scripts that people assisting you could run to create the basic table structures? That way they can offer better help.

At this stage, I am going to step aside for someone with specific SQL server experience. I am pretty certain that SQL server has a more efficient way of handling the subqueries that you are writing and my suggestion is rather flawed, given what you are trying to do. They are likely to make a slow query even slower.

I will keep an eye on this question and if I have a moment later on and someone has not stepped in, I will take another look and improve what I have written so far. As it stands, I do not feel as though I am being of much assistance to you, sorry to say.

Edit: Amended my comment regarding use of sub queries.

WeTi, what would you like to achieve?

LEFT JOIN could work if each of the above queries returns just one row. If any of the queries will return more rows then you have to be ready for many repeated values in the result... E.g. if three queries will return 3 rows each then LEFT JOIN causes the result set will contain 9 times more rows comparing to single row from each query.

You may test it on a small sample, e.g.

DECLARE 
	@SCustomerID varchar(30) = '56201710194';
	DECLARE
	@SSN varchar(30) = '1123560344'
	
	Select * from Data_Person dp
	CROSS JOIN (Select * from Data_Address WHERE CustomerID= @SCustomerID) a
	CROSS JOIN (Select * from Data_Email WHERE CustomerID= @SCustomerID) b
	CROSS JOIN (Select * from Data_Employment WHERE CustomerID= @SCustomerID) c
	CROSS JOIN (Select * from Data_Membership WHERE CustomerID= @SCustomerID) d
	CROSS JOIN (Select * from Data_Telephone WHERE CustomerID= @SCustomerID) e
	WHERE dp.CustomerID= @SCustomerID

Select allOpen in new window

How many rows this query returns?

I think you're going to slam face first into a Cartesian Product here.

If you can elaborate on what you need to accomplish that's helpful.  Otherwise with each join, judging from some of the names several are 1<>*, and that's a multiplier each join.

Do you need a report?  Is this for some sort of audit?  If you can elaborate a better approach may be possible.

Back to the original question, you just put "union" between the select queries to add them all to the same results window.

Select * from table1
Union
Select * from table2
Union
Etc...

Yes, this could work if all the tables do have exactly equal structure. I wouldn't expect it.
And even when all the tables would have some common columns then you'll obtain a mix of telephones, addresses, employments etc. etc. where you must have clear idea how to use such data set.

To do something like this you would need e.g. following query

SELECT 'Person' as RowType, CustomerID, Name as Value1, LastName as Value2 FROM Data_Person WHERE CustomerID= @SCustomerID
UNION 
SELECT 'Address' as RowType, CustomerID, Street as Value1, City as Value2 FROM Data_Address WHERE CustomerID= @SCustomerID
UNION  --etc.

Select allOpen in new window

The first query must predefine the Value1 and Value2 (and possibly more) columns dimensioned sufficiently and then you have all necessary data on output in one data set suitable for reporting.

how do i put this under the cross join query:
Select * from History_Work where pxHistoryForReference in (Select InsKey from Work where ContactId= @SCustomerID)

The left join works, but query is taking too long time to execute, it runs 15 minuters now still not finished, I havent really checked the result too, but its not empty like Cross join.

Do some of the queries joined by the LEFT JOIN return many rows? If yes and the number of rows is 10 for let say 5 queries then the number of output rows is 10 * 10 * 10 * 10 * 10.

So please execute the original set of 22 standalone queries and post the number of rows returned. If you also post the number of columns in each result set then we may decide about different approach.

The CROSS JOIN is empty because one of the result sets returns 0 rows.

You should start to play with a few tables and then you may add more if the results are OK.

True, I commented out some query with nothing returns results, now it shows result, but its still slow... I don't think this will be faster tho

its crazy shit... the query took 40gb disk space and still taking, I canceled the process due to no space...

Yes, that's the problem of Cartesian product when you use many joins... Do you have an idea what should be on the output?
Do you have an idea what is the number of rows from the single queries?

Well first ten select the row details are same, same columns,  however the row info could be diffrent, the columns starts to change from there, then there are alot of diffirent columns and rows detail after that.

OK. You still did not answer the question about the number of rows from each query. Just remember if just 2 from your queries would return 1000 rows then the result set will count 1000000 rows and that's not suitable for any reporting or additional processing.

More important question is: Do you think it is worth to mix all the results in one result set? All report engines can print 1:N reports from several data sets. It means you can have one header row and many detail rows in one report. Some engines do support several detail data sets. etc.

The problems of this approach were pointed out from the outset by several experts, including me. It is unsurprising to hear that the query is taking so long to run and requires so many resources. Simplify it.

I am closing this question, thanks all the support, now I will use powershell to set 22 variables and 22 select and run one by one, well the assignment was to run select *, then I will instruct to do so, if it returns with 1000 or 4000 results then instructor didn´t care about it, and I already filled out the harddrive to 40gb by using the left join or cross join or inner join, that I tried. So im going to do this in powershell instead, however the code will be very very long, but that is the only way for now.

Did you not see my post about "union all"?

The UNION approach is commented right after your post. You did not offer any solution when tables do have different structures. We also have more serious problems in the number of output rows...

Union didn't work, it returned error, and yes I tried. Pcelba come very close to the answer, but then the select starts to fill disks... well just as Pcelba said, the columns and rows is beening 10x more, if the result become more and more due to the Select *. Well now Im going to other method than SQL query. Hope it works better.

Again thanks all