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

Query for fetch two tables data combinedly

 
Ranch Hand
Posts: 630
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
Hi,
I have 2 tables.

Consider:-
Table 1 name 'student' have columns like follows


Table 2 name 'exam_dates' have columns like follows



question is how I can show each roll_no's last date  of exam while selecting roll_no from table 1 via select query?

I tried


But it shows all rows.

I want result like this after run only 1 query:-


How I can do that?
 
Saloon Keeper
Posts: 7582
176
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
It'll involve an additional condition along the lines of

AND date = max(SELECT date FROM exam_dates WHERE ...)

Filling in the dots is left as an exercise to the reader :-)
 
Mandar Khire
Ranch Hand
Posts: 630
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
Thanks Tim Moores,
I used as follows


And it shows result as I want.


Now additional question,

Current Result show me Last paper date for each student from combined two table entries.
How Can I more filter this result that, last paper date should not be or less than today's date?
So If any student have his/her last paper before or on today's date, then his row should not be in result.

I just know that for compare dates I have to use CURDATE().
But How I dont know?
Thanks,
 
Tim Moores
Saloon Keeper
Posts: 7582
176
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
Sounds like an additional condition is needed that selects only those rows where curdate() < max(b.date).
 
Mandar Khire
Ranch Hand
Posts: 630
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
I checked previous query,
It show wrong output.

I have 2 tables,
table1 student


table 2


Now as per query


I should get result as
1. Roll_no 111 have Maths at 2018-06-20
2. Roll_no 112 have History at 2018-06-22

but I am getting result as follows:-


My query is


Where I am doing wrong?
If this works perfect then I will go for additional filtering, that given where curdate() < max(b.date) not working perfectly as I thought.
Might due to first result is wrong so it not working.
Thanks,
 
Tim Moores
Saloon Keeper
Posts: 7582
176
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
Have you tried what I suggested in my first post?
 
Mandar Khire
Ranch Hand
Posts: 630
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
Yes,
I tried.


It shows error

I dont know how to use your first post.
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
SELECT MAX(date) FROM exam_dates

 
Mandar Khire
Ranch Hand
Posts: 630
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
I changed my query as follows:-


Now I got correct Maths paper dates for both students.
Result as follows:-

I tried many ways
But not found way to get result which I am looking.
Result I required as follows:-
1. Roll_no 111 have Maths at 2018-06-20
2. Roll_no 112 have History at 2018-06-22

Need help.
 
Mandar Khire
Ranch Hand
Posts: 630
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
First I check whether 2 table shows combined result of 4 rows.
Query 1


It shows all 4 rows.

Then I check 'group by'
query 2


it shows result which I repeatedly getting.
Both students Maths paper dates.

I not understand whether 'and (SELECT MAX(date) FROM exam_dates)' is working or not!

Need Help to understand how & why.
 
Tim Moores
Saloon Keeper
Posts: 7582
176
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator

Mandar Khire wrote:I not understand whether 'and (SELECT MAX(date) FROM exam_dates)' is working or not!


You missed the "WHERE ..." part of what I wrote. Knowing the max date overall doesn't help you much, since you need it for each student separately.
 
Mandar Khire
Ranch Hand
Posts: 630
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator

Tim Moores wrote:

Mandar Khire wrote:I not understand whether 'and (SELECT MAX(date) FROM exam_dates)' is working or not!


You missed the "WHERE ..." part of what I wrote. Knowing the max date overall doesn't help you much, since you need it for each student separately.



I dont know what should I write after Where ...?
Can you give me complete query, so I can study it, understand it?
 
Mandar Khire
Ranch Hand
Posts: 630
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
Query works:-


But question is without using 'join' can we write query for getting same result?
Thanks,
 
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
Why do you need to rewrite the query without a JOIN?
 
Mandar Khire
Ranch Hand
Posts: 630
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator

Knute Snortum wrote:Why do you need to rewrite the query without a JOIN?



I am using Hibernate3.jar & related libraries, & in it I get exception as follows:-


So I am searching is there any way to get same result without join, or with join how to get same result in Java program by using hibernate3.
 
Mandar Khire
Ranch Hand
Posts: 630
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
I asked question related to Hibernate & Join query.
Join query & hibernate 3!.
 
Don't get me started about those stupid light bulbs.
    Bookmark Topic Watch Topic
  • New Topic