question

Kalpana-3650 avatar image
0 Votes"
Kalpana-3650 asked EchoLiu-msft commented

How do I merge 2 select statements ( one which has alot of left joins and another simple select statement ) both with different columns

Hi

I have got this table over here

[1]: /answers/storage/attachments/116385-be6c2f7zq3c71.png


The right 3 columns has to appear as rows, I thought a left join would achieve it but I think an union would do it. I need advice on how can I achieve this as I understand union has to have the same number of rows, I added fake column but I am still getting errors.

 select d.PlanDate, d.ProcessUnitID, d. xProfID, (d.DActualcumtotal - d.DTargetcumtotal) as Result, x.xProfileID, x.JobOrderName,x.WorkNumber, p.ProcessUnitName, i.TotalAvailable, i.InvxID, i.InvProcType, c.CoDate,c.CoTotalAmount,c.CoxProfID from FullTable d
 LEFT JOIN xProfile x
 ON d.xProfID = x.xProfileID
 LEFT JOIN ProcessUnit p
 ON d.ProcessUnitID = p.ProcessUnitID
 LEFT JOIN Inventory i
 ON d.xProfID = i.InvxID and d.ProcessUnitID=i.InvProcUnit and d.PlanDate = i.InvDate
 LEFT JOIN Counting c
 ON d.xProfID = c.CoxProfID and d.PlanDate = c.CoDate
 where d.PlanDate = '2021-07-09' 
 order by d.xProfID

Basically, I need the counting table columns to appear as rows if the c.CoDate matches the where clause. How can I achieve this ?
Right now, I am getting this error
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

sql-server-transact-sql
be6c2f7zq3c71.png (42.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm not seeing a union anywhere...

The queries within a union need to have the same number of columns... your query above has 14 columns so make sure your other query has 14 columns...

0 Votes 0 ·

Could you have any update?Did the following methods solve your problem?If not, please provide more details.

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Kalpana-3650,

116543-image.png

The information you provided is not displayed. And we did not see the union statement. If you want to merge two select statements, the easiest way is to use union. When using the union statement, in addition to ensuring that the number of columns after the two select statements are the same, it is also necessary to ensure that the data types of the corresponding columns in the two select statements are the same.

After ensuring that these operations are correct, if you still encounter problems, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result? So that we’ll get a right direction and make some test.

Of course, you also need to provide us with the query you are trying.


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (13.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.