CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Wesley Anthony
Wesley Anthony

Reputation: 1

Joining tables in oracle 11g xe

Can someone please explain why whenever I try to join columns of two different tables in oracle 11g xe it says no data found, yet the tables are populated.

SELECT
    c.name
FROM
    BOOK c
JOIN 
    STUDENT co ON c.name = co.name;

Okay so I added more information on this question as I am still stuck. Basically I need to join certain columns of different tables together

I need to join the name and surname from student table, name from book table, name from author table

[Student table][1]

[book table][2]

[author table][3]

[1]: https://i.sstatic.net/paIeB.jpg
[2]: https://i.sstatic.net/06CeV.jpg
[3]: https://i.sstatic.net/JUnkI.jpg

Upvotes: 0

Views: 118

Answers (2)

Littlefoot
Littlefoot

Reputation: 142938

(I borrowed MT0's tables; thank you).

You already know that (inner) join returns nothing:

SQL> select c.name
  2  from book c join student co on c.name = co.name;

no rows selected

As you were told, at least 3 times, there are no common values in both table's NAME columns. It doesn't matter they are populated, it maters with what.

However, there are joins that will return something - outer and cross joins. For example:

SQL> select c.name
  2  from book c LEFT join student co on c.name = co.name;

NAME
-------------------
Charlotte's Web
Alice in Wonderland
The BFG

SQL> select co.name
  2  from book c RIGHT join student co on c.name = co.name;

NAME
-----
Alice
Carol
Beryl

SQL> select c.name
  2  from book c CROSS join student;

NAME
-------------------
Alice in Wonderland
Alice in Wonderland
Alice in Wonderland
The BFG
The BFG
The BFG
Charlotte's Web
Charlotte's Web
Charlotte's Web

9 rows selected.
    
SQL> select c.name
  2  from book c join student co on utl_match.jaro_winkler_similarity(c.name, co.name) > 70;

NAME
-------------------
Alice in Wonderland
Charlotte's Web

SQL>

As you can see, if you modify the query a little (or a little more), there might be some result.

Therefore, consider posting your tables' contents. If they contain a lot of data, create an excerpt (or provide a script with CREATE TABLE and INSERT INTO statements), something we can work with.

Upvotes: 0

MT0
MT0

Reputation: 168232

If they do not have any rows where the name of the student matches the name of the book then there will be zero rows in common.

For example:

CREATE TABLE student (name) AS
SELECT 'Alice' FROM DUAL UNION ALL
SELECT 'Beryl' FROM DUAL UNION ALL
SELECT 'Carol' FROM DUAL;

CREATE TABLE book (name) AS
SELECT 'Alice in Wonderland' FROM DUAL UNION ALL
SELECT 'The BFG' FROM DUAL UNION ALL
SELECT 'Charlotte''s Web' FROM DUAL;

Then:

SELECT
c.name
FROM BOOK c
JOIN STUDENT co ON c.name = co.name;

Outputs:

NAME

db<>fiddle here

Upvotes: 2

Related Questions