LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 03-13-2012, 03:12 PM   #1
Thor_2.0
Senior Member
 
Registered: Nov 2007
Location: Somewhere on my hard drive...
Distribution: Manjaro
Posts: 2,227
Blog Entries: 23

Rep: Reputation: 279Reputation: 279Reputation: 279
Smile Sql joins, confused


Hi!

This one's for the database buffs around here.

Consider these

Quote:
tblCountries
ID numeric
name varcher

tblPeople
ID numeric
countryid numeric
(more fields)
tblCountries has all the names of the countries, someone lives in a country. Database normalisation dictates that data should NOT repeat itself, hence the need for a table where countries are listed, and a link (tblCountries.ID - tblPeople.countryid). There are no database links, the link is fictive. I do not like to make links the way access did, that's too rigid...I let the software/SQL handle that.

How to I list the country (among other fields) of the people from the countryid?

I understand this could be a join, but joins have always been illusive to me.

Thanks for shedding some light. Of course, if there's a link to a good tutorial, I'll have learned to fish, and I'll eat for the rest of my life

Thor
 
Click here to see the post LQ members have rated as the most helpful post in this thread.
Old 03-13-2012, 06:03 PM   #2
kbp
Senior Member
 
Registered: Aug 2009
Posts: 3,758

Rep: Reputation: 644Reputation: 644Reputation: 644Reputation: 644Reputation: 644Reputation: 644
I use w3schools when I forget stuff - http://www.w3schools.com/sql/sql_join.asp
 
1 members found this post helpful.
Old 03-13-2012, 06:26 PM   #3
sundialsvcs
Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 5,455

Rep: Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172
SELECT DISTINCT co.name as CountryName, pp.id as PersonID
FROM tblPeople pp
INNER JOIN tblCountries co ON (pp.countryID = co.ID)

The concept is simple ... keep it that way.

When you write a SELECT, you can draw information from as many tables as you wish. The only thing that you need to do is to specify what the various tables have in common. The database manager will match-up the rows from the various sources, filter it down to only DISTINCT rows if you wish, then select from the matched-up rows.

Be aware that if, say, the "left side" table contains (say) 2 occurrences of a particular value, and the "right side" table contains (say) 3 occurrences, the result-set will contain "2 * 3 = 6" rows having that value. It can add up very fast.

Normally, the join is an "inner" join. That is, it includes only those rows which exist in both tables. But you can also have "outer" joins, either "left" or "right." A "left" outer-join always includes all of the rows from the table on the "left" side of the join whether or not those rows have matching rows in the table on the "right" side, and vice-versa.

Notice also that it is possible to join tables without specifying an ON (or, in mySQL, USING...) clause, in which case you get a cartesian product: every row matched with every row. (Not a good thing if both tables contain 50,000 rows each. "You asked for it. You got it. All 2,500,000,000 rows of it. Your system died, of course, but... you asked for it, and you got it.")

Last edited by sundialsvcs; 03-13-2012 at 06:29 PM.
 
2 members found this post helpful.
Old 03-13-2012, 06:29 PM   #4
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,005
Blog Entries: 11

Rep: Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903
Another classic link: http://sqlzoo.net/3b.htm

[edit]Awwww shucks ... sundial beat me w/ a direct example, rather than a link ;}[/edit]


Cheers,
Tink

Last edited by Tinkster; 03-13-2012 at 06:30 PM. Reason: [edit]
 
1 members found this post helpful.
Old 03-14-2012, 07:04 AM   #5
Thor_2.0
Senior Member
 
Registered: Nov 2007
Location: Somewhere on my hard drive...
Distribution: Manjaro
Posts: 2,227
Blog Entries: 23

Original Poster
Rep: Reputation: 279Reputation: 279Reputation: 279
Thanks for the input...and the bookmarks, they're added, of course...

Off to the phpAdmin to try this out.

Thor
 
Old 03-14-2012, 09:07 AM   #6
sundialsvcs
Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 5,455

Rep: Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172Reputation: 1172
Quote:
Originally Posted by Tinkster View Post
Another classic link: http://sqlzoo.net/3b.htm

[edit]Awwww shucks ... sundial beat me w/ a direct example, rather than a link ;}[/edit]


Cheers,
Tink
Writing and teaching two semesters of community college classes on databases makes you pretty good at extemporaneous coding.
 
Old 03-14-2012, 09:41 AM   #7
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,692

Rep: Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987Reputation: 1987
Did you also teach a thesaurus class as well or just a fan of Roxanne ... hehe
 
  


Reply

Tags
joins, sql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL update statement to SQL SERVER 2005 scheidel21 Programming 2 11-05-2009 07:30 PM
SQL noob question: can SQL effectively handle a 2D matrix? JoeyAdams Programming 2 03-17-2008 02:10 AM
LXer: Sql - stored procedures, views, and dynamic sql generation LXer Syndicated Linux News 0 08-17-2006 07:33 AM
Migrating from MS-SQL server to My-SQL emailssent Linux - Networking 2 02-07-2005 03:20 PM
Which SQL is suitable for EMbedded SQL on C? hk_michael Programming 4 01-10-2005 06:07 PM


All times are GMT -5. The time now is 05:44 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration