Select a query with many-on-many table

Question

I have a dbo.ArtikelAlternatief table created like this:

CREATE TABLE [dbo].[ArtikelAlternatief]( 
    [Barcode] [varchar](50) NOT NULL,
    [BarcodeAlternatief] [varchar](50) NOT NULL,   
 CONSTRAINT [PK_ArtikelAlternatief] PRIMARY KEY CLUSTERED 
(
    [Barcode] ASC,
    [BarcodeAlternatief] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now I want the following results to be combined:

select BarcodeAlternatief AS 'Barcode' from dbo.ArtikelAlternatief where Barcode like '7630015711115' 
select Barcode AS 'Barcode' from dbo.ArtikelAlternatief where BarcodeAlternatief like '7630015711115'

How can I combine these 2 queries into a single column of results?

Answer 1

You can do this in 3 methods.

SQLFIDDLE

Method 1: Using CASEStatement:

select 
     (case when Barcode = '7630015711115' 
            then BarcodeAlternatief
            else Barcode END) as 'Barcode'  
    from ArtikelAlternatief
    where Barcode = '7630015711115'
          or BarcodeAlternatief = '7630015711115';

---

Method 2: You can try using the DECODE(Ofacle) operator ,

SELECT  DECODE (BarcodeAlternatief , '7630015711115', Barcode , BarcodeAlternatief ) AS Barcode 
FROM dbo.ArtikelAlternatief 
where Barcode = '7630015711115' OR BarcodeAlternatief = '7630015711115'

---

Method 3:

Try the query with UNION ALL:

select BarcodeAlternatief AS 'Barcode' from dbo.ArtikelAlternatief 
where Barcode = '7630015711115' 

UNION ALL

select Barcode AS 'Barcode' from dbo.ArtikelAlternatief 
where BarcodeAlternatief = '7630015711115'

• If you want to allow duplicates, use UNION ALL. If you do not want to allow duplicates, use UNION.
• = LIKE, , .

Answer 2

UNION:

query1
UNION ALL
query2

ALL , .

Answer 3

U

select BarcodeAlternatief AS 'Barcode' from dbo.ArtikelAlternatief where Barcode like '7630015711115' 

Union All

select Barcode AS 'Barcode' from dbo.ArtikelAlternatief where BarcodeAlternatief like '7630015711115'

Answer 4

"OR" "CASE":

select 
     (case when Barcode like '7630015711115' then BarcodeAlternatief
           when BarcodeAlternatief like '7630015711115' the Barcode 
            else '') as 'Barcode'  
    from dbo.ArtikelAlternatief
    where Barcode like '7630015711115'
          or BarcodeAlternatief like '7630015711115'