CODEWITHSUNDEEP
mysqlsqldatabaseunion
mrjasmin
mrjasmin

Reputation: 1270

SQL- JOIN two column from two tables

I need help to formulate a query. I want to select all DISTINCT colors from TABLE A and TABLE B.

This is the wanted result from the query:

BLACK
RED
YELLOW
BLUE

I have these tables:

TABLE A

ID   NAME    COLOR
5    SOCKS   BLACK
4    SOCKS   RED

TABLE B 

ID   NAME   COLOR 
0    CAR    BLUE
1    BIKE   BLUE
5    TRUCK  YELLOW
10   PLANE  NULL

I have tried:

SELECT DISTINCT A.color FROM A JOIN B ON b.color

But I don't get all the colors. :(

Upvotes: 1

Views: 4329

Answers (5)

Mo Kouli
Mo Kouli

Reputation: 112

SELECT DISTINCT color FROM 
(
    (
        (SELECT color FROM A WHERE color IS NOT NULL)
        UNION 
        (SELECT color FROM B WHERE color IS NOT NULL)
    ) AS temp
)

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

SELECT color FROM A
UNION
SELECT color FROM B
WHERE  color IS NOT NULL -- only filters NULL from B
ORDER  BY color          -- sorts all rows

UNION (instead of UNION ALL) removes duplicates. No need for additional subqueries or DISTINCT.

Upvotes: 7

JonH
JonH

Reputation: 33143

SELECT 
  A.Color FROM TableA A
WHERE A.Color IS NOT NULL
UNION
SELECT
  B.Color FROM TableB B
    WHERE B.Color IS NOT NULL

I removed DISTINCT because it is not necessary when you combine it with UNION - I always tend to forget this!.

Upvotes: 4

Jake Feasel
Jake Feasel

Reputation: 16955

http://sqlfiddle.com/#!2/0de4d/5

SELECT distinct
    COLOR
FROM
    (
      select color from TableA
      UNION
      select color from TableB
    ) tmp
WHERE color IS NOT NULL
order by color

edited to remove nulls

Full disclosure - SQL Fiddle is my site

Upvotes: 1

Paul Grimshaw
Paul Grimshaw

Reputation: 21024

Try:

SELECT DISTINCT Color
FROM (
    SELECT Color FROM TableA
    UNION ALL
    SELECT Color FROM TableB
) Colors
WHERE NOT Color IS NULL

Upvotes: 1

Related Questions