• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Question for SCBCD aspirants (and gurus)...

 
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have two tables (and corresponding entities) - Department and Employee where there is a bidirectional one-to-many relationship between them. (One Dept and multiple employees).

Now, dept D1 has employees E1 and E2, while D2 has E3 and E4. (That is, there are two rows D1 and D2 in Dept table and 4 rows E1-E4 in Employee table).

Question - How many number of rows will be returned by the following queries?

1. SELECT d from Employee e INNER JOIN e.departmant d

2. SELECT d from Department d INNER JOIN d.employees e
 
Ranch Hand
Posts: 368
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tried this out and found:

1. SELECT d from Employee e INNER JOIN e.departmant d


Would return 4 rows.

2. SELECT d from Department d INNER JOIN d.employees e


Would return 2 rows.
 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
i was wondering both query select department, why it return different row result?

Thank You
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
pb sql . no jpa
The INNER JOIN keyword return rows when there is at least one match in both tables.
1. select :
because root query == Employee e. ==>in database:e1[inner d1 OK ],e2[inner d1 OK ],e3[inner d2 OK ],e4[inner d2 OK ] . so result d1,d1,d2,d2 ( because select ==d)
2. select :
because root query == Employee d. ==>in database:d1[inner e1 or e2 OK], d2[inner e3 or e4 OK]. so result d1,d2 ( because select ==d)
 
philippe pele
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
to be clearer. we must imagine that when you send a query to the database. This one creates a virtual table, from the root (employe e).
first : database create root table ( from employe) (then filters the content based on conditional (if any).)
---
e1
---
e2
---
e3
---
e4
second add joins ( remove lines not conforming to the inner)
---
e1 |d1
---
e2| d1
---
e3| d2
---
e4| d2
finally .from the result of the root table, returns the select result .
 
philippe pele
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
correction :pb sql . no jpa


because, during the examination 310-094/1Z0-898, ​​I had a very very strong sense of deja vu. A word to the wise
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic