• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Single Query from 2 tables

 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have the following tables(country,costs) with the corresponding columns data.

country
code name
us United states of America
sn Senegal
gh Ghana


costs
source destination cost
us us 10
us sn 50
us gh 40


Query result
source_cname destination_cname cost
United states of America Ghana 40

I want to display the above query result.
I did 'select source,destination from costs where cost=40;' but couldn't retrieve the associated names in the country table. I want to achieve this with one select query statement.






 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You need to use 2 inner joins on the country table: one for the source and one for the destination.

Something like

Hope it helps!
Kind regards,
Roel
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Roel, The solution works perfectly.



 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Ngom wrote:Thanks Roel, The solution works perfectly.

Glad to hear I could help!

But much more important: do you understand why this solution works and why you need 2 (inner) joins to get the desired result?
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But much more important: do you understand why this solution works and why you need 2 (inner) joins to get the desired result?


Frankly speaking, no. Can you give the explanation, please?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Ngom wrote:Can you give the explanation, please?

Sure!

First of all, do you know what's an (inner) join? If not, here you'll find a great explanation with an example (and you can even try it yourself). Carefully read this article and then you can tell me why you need this inner join in your scenario.
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have dealt with single inner join but i did not know i could have multiple ones with a select.




 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Ngom wrote:I have dealt with single inner join but i did not know i could have multiple ones with a select.

Ok! Then we can proceed immediately to the only remaining question: why you need 2 of them in this case?

Which query would you write to get the following query result?
source_cname cost
United states of America 40
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select name as source_name,cost from country,costs where country.code=costs.source and cost=40;

 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Ngom wrote:select name as source_name,cost from country,costs where country.code=costs.source and cost=40;

Spot-on!

Now what happens if you add name as destination_name to the select list of this query?
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Probably i will have 'United states of America' printed under destination_name.





 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Ngom wrote:Probably i will have 'United states of America' printed under destination_name.

Not probably, but certainly

Now assume you have 2 seperate country tables: 1 with all source countries and 1 with all destination countries.
country_source
code name
us United states of America

country_destination
code name
us United states of America
sn Senegal
gh Ghana

Which query would you write now to get the following query result?
source_cname destination_cname cost
United states of America Ghana 40
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I may not have understood you question well but if those are the only available 2 table then the query to produce the result you have given could be:

select cs.name as source_cname,cd.name as destination_cname,40 from country_source cs,country_destination cd where cd.code='gh';





 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Ngom wrote:I may not have understood you question well but if those are the only available 2 table then the query to produce the result you have given could be:

I forgot of course to mention that the costs table is still available (but doesn't change and that's why I didn't list this table). Sorry about that!

So can you adjust your query?
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, I cannot. Any hint? :-)




 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Ngom wrote:No, I cannot. Any hint? :-)

Of course! Using tables country_source, country_destination and costs:

1/ write a query to show source_name and cost (if cost is 40)
2/ write a query to show destination _name and cost (if cost is 40)
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1) select name as source_name,cost from country_source,costs where country_source.code=costs.source and cost=40;
2) select name as destination_name,cost from country_destination,costs where country_source.code=costs.destination and cost=40;

Then what do i do next if i am correct?






 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Ngom wrote:Then what do i do next if i am correct?

Looking good!

We are almost there! Now you can combine both queries into 1 query: just merge the selected columns into 1 select list, merge the tables into 1 from clause and merge all conditions into 1 where clause. Here's a (very simple) example:
query1: select col2a, col2b from table1, table2 where table1.id = table2.id
query2: select col3a, col3b from table1, table3 where table1.id = table3.id
=> combined query: select col2a, col2b, col3a, col3b from table1, table2, table3 where table1.id = table2.id and table1.id = table3.id

One remark: don't forget to qualify (with the table name or an alias) your column names when needed. It's required when a column name you are using in your query (in select list or in where clause) is defined in more than 1 table. In the above example, the column id appears in every table (table1, table2 and table3), so if I don't qualify id using the table name (or an alias if you have very long table names or you are just lazy ) each time, the database doesn't know which id I'm referring to.

Now it's up to you! Try to combine your 2 queries into 1.
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select cs.name as source_name,cd.name as destination_name, c.cost from country_source cs,costs c,country_destination cd where cs.code=c.source and cd.code=c.destination and c.cost=40;

Am i right, Roel?




 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Ngom wrote:select cs.name as source_name,cd.name as destination_name, c.cost from country_source cs,costs c,country_destination cd where cs.code=c.source and cd.code=c.destination and c.cost=40;

Am i right, Roel?

Excellent!

Now the final step. Until know we had for education purpose 2 seperate country tables (one for destination and one for source), but in the actual database we have just 1 country table which can be used for both destinations and sources. But luckily for us (you), all these tables (country, country_source and country_destination) have the same table structure (column names), so the only thing you need to change are the table names of country_source and country_destination in the from clause and use country instead (because that's the only one table which actually exists in the database ).

Ready, set, go!
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think the final answer is:
select cs.name as source_name,cd.name as destination_name, c.cost from country cs,costs c,country cd where cs.code=c.source and cd.code=c.destination and c.cost=40;

Thanks a lot Roel for the time you spend with me in teachimg me that. I do appreciate it. It is getting to midnight at my side and i am readying to leave you now. Goodbye and i hope to have you another time.




 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Ngom wrote:I think the final answer is:
select cs.name as source_name,cd.name as destination_name, c.cost from country cs,costs c,country cd where cs.code=c.source and cd.code=c.destination and c.cost=40;

That's indeed the query you were looking for in your original post and if you run this query it should give you the same result. Well done! How does it feel your first handcrafted query with 3 tables?

As a final note: this query and the query I suggested in my 1st post are equivalent, but there's probably a difference in performance (certainly when tables contains millions of records). I'm not gonna bother you with these details as this is a whole discussion on its own.

Paul Ngom wrote:Thanks a lot Roel for the time you spend with me in teachimg me that. I do appreciate it.

Glad I could help! I hope you know understand why you need to include the country table 2 times.

Paul Ngom wrote:It is getting to midnight at my side and i am readying to leave you now.

Here it's almost 1:00 am
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic