Desperately seeking some friendly SQL advice

dancahill

Hi all,

I am finding that what I previously thought of as being a basic but adequate knowledge of SQL is turning out to be below par :eek:

As such I'm looking for some advice first of all specific and then general. Appreciate as much help as possible.

1st I am using natural joins in SQL statements between various tables and I normally construct them like this:

SELECT candidate.field1, candidate.field2, location.location
WHERE candidate.locationID = location.locationID....;

.....etc.

Now my problem is that when I am trying to get result records and there is a NULL value for say candidate.locationID in a particular record, then it won't give me any output for that record only bringing up records where there is a value for this ID present.

Is there a way round this? it seems that it is requiring the presence of a value in any and all fields specified in natural joins.

More generally I notice alot of pretty complicated SQL on the board LEFT JOINS & INSIDE-OUT JOINS (🙂 ) etc. which I would like to know more about - don't really want to splash out on a book are there any recommended SQL sites (apart from mySQL.com) that anyone can point me towards.

MUCH APPRECIATED!!!!

Dan.

dancahill

Sorry I've sorted it with LEFT JOIN - being lazy I guess, getting tired.

Anyway still appreciate any pointers on some user friendly SQL sites if any.

Tks

MrAlaska

I have not seen a page that illustrates all the different joins in a clear and consise manner, but I am getting subscribed to this thread in case somebody posts one.

Did you try LEFT OUTER JOIN?

That should populate the contents of the right side table with null values if a match does not exist.

http://www.mysql.com/doc/en/JOIN.html

EDIT: Oh, LEFT JOIN fixed it, I read your comment wrong. I think I am getting too tired to think in SQL!

mikesgt2

MrAlaska...

Here is the perfect explanation of different JOIN types:

http://forums.devshed.com/showthread.php?threadid=25949#post101105

MrAlaska

Thanx mikesgt2. It was tripping me up for a second what the difference was between a LEFT JOIN and a LEFT OUTER JOIN. Apperently there is no difference, LEFT OUTER JOIN is just a provided syntax for ODBC compatibility. I think the aliases and naming conventions are more confusing than the concepts lol

dancahill

Thanks guys - that is very clear now. I find the mySQL site a little challenging after a few hours on the PC - why do people who write documentation seem to have to be so pompous?

BTW have you ever read CJ Date's intro to database systems? - that has the effect of making me no longer understand things I understood quite well before reading!!!! 😕

Dan.

ultraslacker

I just love this site -

SQL Zoo