CODEWITHSUNDEEP
mysqlsqljoin
Kern Elliott
Kern Elliott

Reputation: 1659

Join 2 tables and extract one column based on where clause

I have 2 tables both hold track_ids and artist_ids. What I would like to do is join all the track_ids to see which artist_ids correspond with what track. The result I want is that if I search for an artist_id it should return results from both tables, in other words I need to join both tables and search for an artist_id. If the artist_id is found then it should return the specific track. This result should also be unique.

Here is how the tables look with data

Table1: 
Column names: id [primary key], artist_id, track_id
Column values: 1, 34, 28
Column values: 2, 34, 11
Column values: 3, 34, 33

Table2: 
Column names: id [primary key], track_id, artist_id, 
Column values: 11, 11, 2
Column values: 12, 12, 24
Column values: 13, 13, 2

So in the example above if I am looking for artist_id 34 the query should combine the 2 tables and then search for artist_id 34 and return track_ids 28,11,33.

Upvotes: 1

Views: 140

Answers (2)

GarethD
GarethD

Reputation: 69789

You could Just UNION your two tables together. The UNION operator by definition returns distinct results:

SELECT  Track_ID
FROM    Table1
WHERE   Artist_ID = 34
UNION
SELECT  Track_ID
FROM    Table2
WHERE   Artist_ID = 34

Upvotes: 1

Oscar Pérez
Oscar Pérez

Reputation: 4397

In the example you give us, there's no need to JOIN the two tables. You just need to execute:

SELECT *
  FROM Table2
 WHERE artist_id = 34

BUT, if you need to JOIN them (in case, for example, you need extra fields from Table1), it's as simple as:

 SELECT * 
   FROM Table1
   JOIN Table2 on Table1.id = Table2.artist_id
  WHERE Table1.id = 34

Upvotes: 0

Related Questions