Which mysql join will be applicable?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

Which mysql join will be applicable?

Post by infomamun »

Hi there,
As I always get confused of mysql join. Here are my descriptions:
I have two tables

Table: price
==============================
ID || company || volume || price
------------------------------------------
01 || comp-A || 300000 || 500
------------------------------------------
02 || comp-B || 260000 || 400
------------------------------------------
03 || comp-C || 300000 || 450
------------------------------------------
04 || comp-D || 280000 || 500
------------------------------------------
05 || comp-E || 275000 || 400
-----------------------------------------
06 || comp-F || 290000 || 450
=============================

Table: type_of_company
===================================
ID || company || comp_type || country
-------------------------------------------------
01 || comp-A || Transport || Canada
-------------------------------------------------
02 || comp-B || Oil & Lubri || Brazil
-------------------------------------------------
03 || comp-C || Transport || India
------------------------------------------------
04 || comp-D || Transport || USA
------------------------------------------------
05 || comp-E || Courier || Bangladesh
------------------------------------------------
06 || comp-F || Transport || Jermany
==================================

user of my website will check which company are of 'Transport' type and their share volume and price. They will select company type from a drop-down menu and after pressing 'SUBMIT' menu, they will see this page:

Type of company: Transport
=========================
Company || Price || Volume
=========================
comp-A || 500 || 300000
----------------------------------
comp-C || 450 || 300000
----------------------------------
comp-D || 500 || 280000
----------------------------------
comp-F || 450 || 290000
=========================


which mysql join/query should use in this case?

Expecting a help from all expert member.

Regards
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Which mysql join will be applicable?

Post by requinix »

From all expert members? Don't hold your breath.

I wouldn't filter using a (regular, vanilla INNER) JOIN but with a WHERE.

Code: Select all

SELECT t.company, p.price, p.volume
FROM type_of_company t
JOIN price p ON t.company = p.company
WHERE t.comp_type = "Transport"
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Which mysql join will be applicable?

Post by califdon »

If, as you say, you "always get confused" by joins, I strongly advise you to take the time to learn what a join is. You won't get very far with databases unless you do. Joins are not very difficult to understand. Simple ones like you need for this example, are so easy that if you have to ask, that means that you haven't tried to study them at all. All you need to do is use Google to search for "sql join" or maybe "sql join tutorial" and read the first few results pages. Spend a half hour doing this and you will learn what joins really are and how to write queries with joins for simple situations. If you later need more complex joins, perhaps with more tables and different types of joins, you can come back and read more, but you need to start with the basics.
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

Re: Which mysql join will be applicable?

Post by infomamun »

Thanks requinix for your mysql query.

for califdon, I said that I already did the query by INNER JOIN but the same thing requinix did by JOIN.
So, same result can be achieved by several queries. So I got confused which one is better and which is not.
Unfortunately, I did not find any online article which describes advantage and disadvantage of each join query.
Like, in my case, what are the merits of INNER JOIN and demerits of same.
Also merits and demerits of only JOIN.
that's why it is confusing. :roll:
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Which mysql join will be applicable?

Post by califdon »

infomamun wrote:Thanks requinix for your mysql query.

for califdon, I said that I already did the query by INNER JOIN but the same thing requinix did by JOIN.
So, same result can be achieved by several queries. So I got confused which one is better and which is not.
Unfortunately, I did not find any online article which describes advantage and disadvantage of each join query.
Like, in my case, what are the merits of INNER JOIN and demerits of same.
Also merits and demerits of only JOIN.
that's why it is confusing. :roll:
There is no difference between JOIN and INNER JOIN. "INNER" is the default, so if you just use "JOIN", it will be an INNER JOIN. As for the other joins, it is never a question of advantages or disadvantages, they all do completely different things, so you either use the correct one or you won't get the results you want. I will admit that the syntax can be confusing, and if your native language is other than English it is probably more difficult. But this is exactly why reading several different tutorials will be of value for you. All this is discussed in several tutorials that I selected at random:
http://www.codinghorror.com/blog/2007/1 ... joins.html
http://www.sql-tutorial.net/sql-join.asp
http://beginner-sql-tutorial.com/sql-joins.htm
http://www.quackit.com/sql/tutorial/sql_join.cfm
http://en.wikipedia.org/wiki/Join_%28SQL%29
http://sqlzoo.net/wiki/The_JOIN_operation
http://www.halfgaar.net/sql-joins-are-easy
http://www.roseindia.net/sql/sqljoin/index.shtml
Post Reply