CODEWITHSUNDEEP
sqloracle-database
user3553846
user3553846

Reputation: 342

SELECT clause using 3 different tables

Having these 3 tables

DEPARTMENT

 //DEPARTMENT
 DNAME
 -----------
 RESEARCH
    IT
  SCIENCE

PROJECT

//PROJECT
P#      TITLE
----------------
1      COMPUTING
2       CODING
3       SEARCHING

DP <-- Assume constraint references done, just i didn't show the code here

 //DP
 DNAME        P#
 ---------------
 RESEARCH     1
   IT         2
 RESEARCH     3

When using this statement

SELECT d.DNAME,P.TITLE FROM DEPARTMENT d 
      INNER JOIN PROJECT 
      INNER JOIN DP ON d.DNAME=DP.DNAME AND P.P#=DP.P#;

How should i change in order to get output like this, only show the DNAME that have projects

DNAME         TITLES
---------------------
RESEARCH     COMPUTING
RESEARCH     SEARCHING
   IT         CODING

Upvotes: 0

Views: 63

Answers (3)

DSS
DSS

Reputation: 7259

you could also try :

MYSQL syntax:

SELECT d.DNAME,P.TITLE from DEPARTMENT d
  INNER JOIN PROJECT P
  INNER JOIN DP ON d.DNAME=DP.DNAME AND P.P#=DP.P#
order by d.DNAME DESC, P.TITLE ASC

Running fiddle

SQL syntax

SELECT d.dname,p.title from DEPARTMENT d,PROJECT P
  ,DP where d.DNAME=DP.DNAME and
  P.P#=DP.P#
order by d.dname desc, P.title asc

Fiddle.

i have no clue why an inner join with three tables and an on condition in the last inner join does not work with MYSQL. Glad though that you have found a solution.

Upvotes: 0

Ravi.
Ravi.

Reputation: 674

Try this query 

SELECT d.DNAME,P.TITLE from DEPARTMENT d
  INNER JOIN DP ON d.DNAME=DP.DNAME
  INNER JOIN PROJECT P ON P.P#=DP.P#
order by d.DNAME DESC, P.TITLE ASC

Upvotes: 1

arunmoezhi
arunmoezhi

Reputation: 3200

Your DEPARTMENT data is already there in DP table. So use it.

select DNAME, TITLE
from DP d, PROJECT p
where d.p# = p.p#
order by 1

Upvotes: 0

Related Questions