1

Sorry I am really a newbie into programming and I am trying to merge two different columns into one column using sql but if it is not possible can it be done using sql code in c#? I have two tables Product1 and Product2, these tables both have CatID.

For Product1, the CatID contains

1
2
3

For Product2, the CatID contains

1
2
3
4
5

The results that I am getting using union is if they have both similar id it will be merge into one and using concat it will duplicate into like

1 1
2 2
3 3
4
5

But the results that I want is the 1 to 3 is from product1 and then the 4 to 8 from product2 like it will continue on counting with no duplicate:

1
2
3
4
5
6
7
8

Is this possible?

regalskr2006
  • 13
  • 4

3 Answers3

0

can you try this query, use union

select * from Product1
union
select * from Product2 NOT IN(SELECT CatID FROM Product1)
Abhishek Sharma
  • 6,689
  • 1
  • 14
  • 20
0

I am not sure what you want but with LINQ you can create a select. If you show us the SQL code it would be very helpful.

Gonzo Gonzales
  • 113
  • 7
  • SELECT concat (product1.id, ' ' , product2.id) FROM product1 JOIN product2 ON product1.id = product2.id order by product1.id asc but i am getting a different result – regalskr2006 Oct 30 '15 at 07:14
  • You need a full outer join as an example in MySQL: SELECT FROM Table A FULL OUTER JOIN Table B ON A.Key = B.Key [JOIN explination](https://www.securesolutions.no/sql-join-types-explained-with-1-picture/) if you want an example in [LINQ](http://stackoverflow.com/questions/5489987/linq-full-outer-join) – Gonzo Gonzales Oct 30 '15 at 07:30
0

Try it like this:

SELECT CatID, ...OtherColumns...
FROM Product1
UNION SELECT CatID, ...OtherColumns...
FROM Product2
WHERE Product2.CatID NOT IN(SELECT CatID FROM Product1)
ORDER BY CatID

(test here: http://sqlfiddle.com/#!9/7887c):

CREATE TABLE T1(ID INT, txt VARCHAR(10));
INSERT INTO T1 SELECT 1, 'test A'
        UNION  SELECT 2, 'test B'
        UNION  SELECT 3, 'test C';

CREATE TABLE T2(ID INT, txt VARCHAR(10));
INSERT INTO T2 SELECT 1, 'test 1'
        UNION  SELECT 2, 'test 2'
        UNION  SELECT 3, 'test 3' 
        UNION  SELECT 4, 'test 4'        
        UNION  SELECT 5, 'test 5';

SELECT ID, txt
FROM T1
UNION SELECT ID, txt
FROM T2
WHERE T2.ID NOT IN(SELECT ID FROM T1)
ORDER BY ID

The result:

ID  txt
1   test A
2   test B
3   test C
4   test 4
5   test 5
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Hmm can i ask what is the union in the insert table for? what does it do? :D – regalskr2006 Oct 30 '15 at 07:18
  • @regalskr2006, "Your" code is just the lower half ;-) The upper half creates two test tables and fills them with test data... – Shnugo Oct 30 '15 at 07:19
  • Ohh i see. I tried this but the ID just keep merging if they are similar – regalskr2006 Oct 30 '15 at 07:26
  • @regalskr2006, Well, I've the feeling that there is some information missing. The name of your ID column (CatID) let's me think, that this is not the ProductID... True? – Shnugo Oct 30 '15 at 07:27
  • @regalskr2006, Just found that you accepted an answer which is almost identical to mine. Glad that you found a solution, – Shnugo Oct 30 '15 at 07:29
  • Hmm Can I ask another question here? It is kinda related to the question that I ask earlier :D – regalskr2006 Oct 30 '15 at 07:32
  • @regalskr2006, Well, if it is really close: yes, otherwise: please ask a new one. – Shnugo Oct 30 '15 at 07:44
  • @regalskr2006, one more hint: I saw that you moved the accepted answer to mine. As you are new to SO, please be aware, that the professionals giving answers here, are hungry for reputation points. It would be very kind of you to vote helpful all answers up and - if an answer helped you to solve your problem - you should mark it as the accepted answer, This will show to others, that this question is accepted. It is up to you which one is "the best" in your eyes, but please donate the others too, Thx! – Shnugo Oct 30 '15 at 07:46
  • @regalskr2006, good to be aware of: http://stackoverflow.com/help/whats-reputation – Shnugo Oct 30 '15 at 07:49
  • Ohh sorry. I am really new to this. Thanks for the reminder :D – regalskr2006 Oct 30 '15 at 09:11