CODEWITHSUNDEEP
sqljoinwhere-clause
user8420047
user8420047

Reputation:

SQL Trouble with Where clause

So I have a question about how I could go about this.

I have a table called PAPER which has a PANUM and TITLE column, another table called AUTHOR which has a PANUM and ACNUM column, and another table called ACADEMIC which has an ACNUM, GIVENNAME and FAMNAME column.

I need to grab all the PANUM's from PAPER written by the academic 'Mark' 'Yee'. Just wondering if there's a way I can grab the ACNUM of the ACADEMIC based on the GIVENNAME and FAMNAME.

So far I have this,

SELECT panum FROM AUTHOR, ACADEMIC
WHERE famname='Yee' AND givename='Mark'

Upvotes: 0

Views: 53

Answers (2)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

Using JOIN You can get the ACNUM as Below Query:

SELECT AUTHOR.panum,ACADEMIC.ACNUM  FROM AUTHOR
Inner Join 
ACADEMIC ON
AUTHOR.ACNUM = ACADEMIC.ACNUM 
WHERE famname='Yee' AND givename='Mark'

Upvotes: 1

Nishant Gupta
Nishant Gupta

Reputation: 3656

Use Join. You will successfully get the ACNUM from the below query:

SELECT ACNUM FROM ACADEMIC
WHERE famname='Yee' AND givename='Mark'

Now you need to modify it a little bit like this:

SELECT AU.PANUM,AC.ACNUM 
FROM ACADEMIC AS AC
INNER JOIN Author AS AU
ON AC.ACNUM = AU.ACNUM
WHERE famname='Yee' AND givename='Mark'

From above you will get all the PANUM associated with the given author.

How Join works:

https://www.w3schools.com/sql/sql_join.asp

Upvotes: 1

Related Questions