Very simple mysql join

Question

Anyone could tell me how to connect these 2 tables. I tried do it myself.. but just wasted a time, i know it's easy but I somehow can't understand it. Table is from my previous question

Table Articles:

    ID     Content   
    1          bla 
    2          blah 
    3          etc.
    4          whatever

Table Similar:

    ID     Similar_ID   
    3          1
    3          2
    4          1
    4          2
    4          3

Connect them how? What result set are you trying to produce? — Joe Stefanelli, Oct 16 '12 at 18:27

score 0 · Answer 1 · answered Oct 16 '12 at 18:27

0

select a.ID,a.Content,s.Similar_ID from 
Articles a inner join Similar s
on a.ID=s.ID

answered Oct 16 '12 at 18:27

AnandPhadke

13,160
5
26
33

1

Its just joining 2 tables based on ID column and getting values. – AnandPhadke Oct 16 '12 at 18:32
Essentially, you are doing an inner join, selecting the row from table Articles and any rows from Similar that have the foreign key equivalent to the primary key in Articles. You are doing an INNER join, which means if the foreign key is null, it will return nothing. – Barry Chapman Oct 16 '12 at 18:35
yes.Its one type of many joins.based on the requirement you can decide which join to use – AnandPhadke Oct 16 '12 at 18:36
@AnandPhadke: so, go explain that in the answer. Also explain _why_ your answer is the answer to the question. – John Saunders Oct 16 '12 at 18:37
Its alredy explained above.pls check – AnandPhadke Oct 16 '12 at 18:39

score 0 · Answer 2 · answered Oct 16 '12 at 18:34

You want to browse the Similar table, and "convert" its IDs (e.g. 3) in Content (e.g. "Blah").

So: SELECT * FROM Similar;

will list all similarities. Since we have two Ids to convert (something is similar to something else), we need two separate JOINS with the same table Articles, and we'll alias them to "a" and "b":

 SELECT a.Content, b.Content
     FROM Similar
     JOIN    Articles AS a      ON (Similar.ID = a.ID)
     JOIN    Articles AS b      ON (Similar.Similar_ID = b.ID)
 ;

The first JOIN "decodes" the ID field of Similar, the second decodes "Similar_ID".

So

3     1

becomes now

Etc.  Blah

Or you can write:

SELECT CONCAT(a.Content, ' is similar to ', b.Content)
FROM... (same query as above)

and get

Etc. is similar to Blah
Blah is similar to whatever
...

Very simple mysql join

2 Answers2