David- Think of A join consists of one circle of records per table An INNER JOIN only gets the results that are in common between 2 tables or all the rows (where the circles intersect) i.e. In an Inner Join both columns of Both Tables MUST have Non Null identical values and Must be = An OUTER JOIN gets all of the records from all tables of the join condition (all records from all circles) In a LEFT JOIN If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table The way I remember is LEFT JOIN=RIGHT NULL (all records from Left Table Circle and Intersection area of all circles)
In a RIGHT JOIN If there is no matching record for the left table in the ON or USING part of a RIGHT JOIN, a row is set to NULL for the record from the left table The way I remember is RIGHT JOIN=LEFT NULL (all records from Right Table Circle and Intersection area of all circles) >From David's Posts it sounds like the resultset returned is too restrictive Time to consider Outer, Left or Right Join- Cheers, Martin- ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, September 03, 2004 1:06 PM Subject: Re: Understanding Joins : Baffled > I don't understand.... what do you mean by "trim out the irrelevant"? If > certain columns aren't important then why are they in your tables? I don't > need any data, only the structures. You do know that you don't need to > post to an actual NG to get your message to the list, right?. You only > need to CC your emails to: > > [EMAIL PROTECTED] > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > "David Rayner" <[EMAIL PROTECTED]> wrote on 09/03/2004 01:02:01 PM: > > > Shawn, > > Will reply directly to NG (just had an access problem today) reason > > am delating post is that one table is huge and I need to > > trim out the irrelevant Thanks so far!!! > > > > > > > > > > > You will normally get better responses if you always CC the list > > in each of your replies (unless you > > > are intentionally going off-list). > > > > > > Yes, it sounds like you are talking about a relationship to me, > > too. If the we had your existing table > > > structures in front of us we could help you to understand just how > > easy this is. This is a very > > > important topic of database design. Please post as soon as you are > able. > > > > > > Shawn Green > > > Database Administrator > > > Unimin Corporation - Spruce Pine > > > > > > "David Rayner" <[EMAIL PROTECTED]>wrote on 09/03/2004 11:15:41 AM: > > > > > > > Shawn, > > > > Will post & email table info later. > > > > > > > > tbl_urldetails is essentially a lookup table containg a list of 30 > or > > > so urls. > > > > > > > > each record in tbl_development references up to 8 of these URLS by > > > > storing the id of the particular record in tbl_urldetails. > > > > > > > > When I retrieve a record from tbl_development, I read the index and > > > > then want to get the full record, I suppose I could do > > > > this by doing a new query of tbl_urldetails (for each referenced > > > > URLid) , but that seems wasteful or is it? > > > > > > > > I suppose I'm talking about is a relationship? > > > > > > > > Surely this a bread and butter thing to do for a database (but I > cant > > > see it?) > > > > > > > > > > > > > > > > > > > You say you have a table that changes how many columns it has? > > > > Please post the results of: > > > > > > > > > > SHOW CREATE TABLE tbl_developent\G > > > > > SHOW CREATE TABLE tbl_urldetails\G > > > > > > > > > > so I can see it too. :-D > > > > > > > > > > Actually, it sounds like you have a normalization problem. Posting > > > > those two table structures will > > > > > give the list some information necessary to help you out. > > > > > > > > > > Thanks! > > > > > > > > > > Shawn Green > > > > > Database Administrator > > > > > Unimin Corporation - Spruce Pine > > > > > > > > > > news <[EMAIL PROTECTED]> wrote on 09/03/2004 09:54:08 AM: > > > > > > > > > > > Hi, > > > > > > > > > > > > I have a main query which can reads a record tbl_development > > > > > > > > > > > > This has a varying number of the following fields > > > > > > > > > > > > IDurl1 > > > > > > IDurl2 > > > > > > ... > > > > > > IDurln > > > > > > > > > > > > The fields contain indexes into another table tbl_urldetails > where > > > > > > each record contains details > > > > > > about each URL eg hyperlink, description > > > > > > > > > > > > Currently I preload tbl_urldetails into a (coldFusion) array and > > > > use > > > > > > the IDurl1, IDurl2 etc to get > > > > > > the required URL details, I know this is cock-eyed way of doing > > > > > > this, but cannot see how to use a > > > > > > JOIN > > > > > > > > > > > > What I GUESS it should like is > > > > > > > > > > > > SELECT * FROM tbl_development as dev > > > > > > INNER JOIN tbl_urldetails AS url ON dev.IDurl1 = url.intID > > > > > > WHERE intDevID = #intDevID# > > > > > > > > > > > > But that only "Joins" one record. and I cant understand how I > > > would > > > > > > access the results of the query > > > > > > > > > > > > Baffled help!!! > > > > > > > > > > > > > > > > > > zzapper (vim, cygwin, wiki & zsh) > > > > > > -- > > > > > > > > > > > > vim -c ":%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) > )Ig|:norm > > > > > G1VGg?" > > > > > > > > > > > > http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips > > > > > > > > > > > > > > > > > > -- > > > > > > MySQL General Mailing List > > > > > > For list archives: http://lists.mysql.com/mysql > > > > > > To unsubscribe: > > > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > -- > > > > Best Regards > > > > David Rayner MSc CEng > > > > Frustra laborant quotquot se calculationibus fatigant pro inventione > > > > quadraturae > > > > circuli. > > > > http://www.rayninfo.co.uk/blogger.html > > > > > > > > > > > > > > -- > > Best Regards > > David Rayner MSc CEng > > Frustra laborant quotquot se calculationibus fatigant pro inventione > > quadraturae > > circuli. > > http://www.rayninfo.co.uk/blogger.html > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
