CODEWITHSUNDEEP
sql
David
David

Reputation: 2721

How to write the right SQL query statement using join condition?

There are three fields in a table, all of them refer the same field in another table, like this:

table1
-------
! a_term_id* ! b_term_id* ! c_term_id* !
!            !            !            !

table2
-------

! term_id ! term_title ! term_description !
! ------- !            !                  !

columns a_term_id, b_term_id, c_term_id all refer to term_id

How should I write the SQL statement to retrieve the three fields' info?

Upvotes: 0

Views: 7788

Answers (4)

piotrm
piotrm

Reputation: 12366

SELECT 
t.a_term_id, a.term_title, a.term_description, 
t.b_term_id, b.term_title, b.term_description,
t.c_term_id, c.term_title, c.term_description
FROM abc_terms t JOIN ( terms_info a, terms_info b, terms_info c )
ON ( t.a_term_id = a.term_id 
AND t.b_term_id = b.term_id 
AND t.c_term_id = c.term_id )

Upvotes: 0

Harry Joy
Harry Joy

Reputation: 59694

I think you need to know how Sql_Join works. Here on W3Schools you can find useful examples.

A simple example:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

EDIT

You can try something like this:

SELECT * FROM tableA 
inner join tableB on tableA.term_id = tableB.term_id 
inner join tableC on tableA.term_id = tableC.term_id;

It an example you can modify as per your need.

Edit 2

SELECT * FROM  tableB 
JOIN tableA AS tableA1 ON tableB.term_id = tableA1.a_term_id
JOIN tableA AS tableA2 ON tableB.term_id = tableA2.b_term_id
JOIN tableA AS tableA3 ON tableB.term_id = tableA3.c_term_id

Upvotes: 1

Ted Hopp
Ted Hopp

Reputation: 234857

Your question is a bit unclear, but I'll guess that you have a table A with three fields, each of which identifies a (possibly different) row from table B. You want to retrieve info from each of those rows of table B based on the field values of a single row of table A.

To do this, you will need to join table A to table B three times, once for each field of table A. Each join should be given an alias and then you can refer to the fields in the joined table by qualified field names.

SELECT b1.info, b2.info, b3.info
FROM   A JOIN B AS b1 ON field1 = b1.field
         JOIN B AS b2 ON field2 = b2.field
         JOIN B AS b3 ON field3 = b2.field
WHERE  ...

Upvotes: 0

Evgenii
Evgenii

Reputation: 37349

Here is an example. Suppose that we have two tables - Employees and Companies:

CREATE TABLE Employees(
   Id int,
   Name varchar(128),
   CompanyId int);

CREATE TABLE Companies( 
   Id int,
   Name varchar(128),
   Address varchar(1024),
   DateFounded datetime);

The following SQL query will join the tables:

SELECT * FROM Employees  
    INNER JOIN Companies 
    ON Employees.CompanyId = Companies.Id

Upvotes: 0

Related Questions