Adding values from a table to selected data

Question

I have 2 tables. One is general and the other is more detailed. I need to use data from both tables. Right now I am using a temporary help table to do some things and then publish the data. However I don't like this solution and I am trying to find a way to do it without the temporary table.

Here is the code so far:

DELETE FROM TableHelp
 INSERT INTO TableHelp (col1, col2, col3, col4)
  SELECT  TableGeneral.col1, TableGeneral.col2, TableGeneral.col3, TableGeneral.col4           
    FROM    TableGeneral
 WHERE     (col2 >= @param1)
 AND   col3 LIKE (@param2)
 Order By CAST( TableGeneral.col1 AS BIGINT)

 UPDATE TableHelp
SET TableHelp.col5 = TableDetail.col10
FROM TableHelp
INNER JOIN TableDetail ON TableHelp.col1 = TableDetail.col1
WHERE TableDetail.col7 LIKE 'condition';

Select * From TableHelp

This seems like it is doing what I need (not completely sure, I have a lot of data, so I didn't check everything).

Is there a way to handle this without using the help table?

Firstly I want to filter data from the general table according to the WHERE clause and @parameters. After that I want to add to the filtered data values from another table according to some condition. Just to be sure I will write what exactly I want:

TableGeneral

col1	col2	col3	col4
1	aaa	aaa	aaa
2	bbb	bbb	bbb
3	ccc	ccc	ccc
4	ddd	ddd	ddd

TableDetaill

col1	col2	col3	col4	...	col7	col10
1	xxx	xxx	xxx	xxx	xxx	value1
1	xxx	xxx	xxx	xxx	condition	value2
1	xxx	xxx	xxx	xxx	xxx	value3
2	xxx	xxx	xxx	xxx	condition	value4
2	xxx	xxx	xxx	xxx	xxx	value5
2	xxx	xxx	xxx	xxx	xxx	value6
3	xxx	xxx	xxx	xxx	xxx	value7
3	xxx	xxx	xxx	xxx	xxx	value8
3	xxx	xxx	xxx	xxx	xxx	value9
4	xxx	xxx	xxx	xxx	xxx	value10
4	xxx	xxx	xxx	xxx	xxx	value11
4	xxx	xxx	xxx	xxx	condition	value12

Output should be

col1	col2	col3	col4	col5
1	aaa	aaa	aaa	value2
2	bbb	bbb	bbb	value4
3	ccc	ccc	ccc	0
4	ddd	ddd	ddd	value12

If I have to use the help db, than okay. However, I was curious if I can do it with some combination of conditions and functions without the table.

Sharp GPT · Answer

It appears that you are attempting to achieve the goal of combining data from two tables TableGeneral and TableDetail based on certain conditions without using a temporary help table in SQL Server. One way to achieve this is by utilizing a combination of SQL JOINs and CASE statements. Here is an example query that demonstrates how to accomplish this: SELECT tg.col1 tg.col2 tg.col3 tg.col4 CASE WHEN td.col7 = 'condition' THEN td.col10 ELSE '0' END AS col5 FROM TableGeneral tg JOIN TableDetail td ON tg.col1 = td.col1 WHERE (tg.col2 >= @param1) AND tg.col3 LIKE @param2 In this query: - We are selecting the columns from TableGeneral (aliased as tg). - Using a JOIN operation to link the records from TableGeneral with TableDetail based on the common col1. - Incorporating a CASE statement to evaluate the condition where col7 in TableDetail is 'condition'. If the condition is met it selects the corresponding col10 value; otherwise it assigns '0'. - Filtering the data from TableGeneral based on the specified conditions. This SQL query eliminates the need for a separate temporary help table while achieving the desired result of combining data from both tables based on specific conditions. Please adjust the query as per your exact column names and requirements. It is recommended to test this query with your actual data to ensure it meets your expectations. Let me know if you need further assistance or have any specific requirements!