Re: Understanding Joins : Baffled
On Fri, 3 Sep 2004 15:47:26 -0400, wrote: A. Any record in tbl_development can be associated with up to 8 URLs that represent virtual tours of the dwelling types in that particular development. B. tbl_urldetails is a table acting as a master list of the virtual tours you have ready to use. The records in tbl_development should reference these URLs. This is what we call a many-to-many relationship and is commonly modeled in a relational database with a 3-table structure. It is a many-to-many because: 1) Each development can associate itself to several (up to 8, you say) URLs 2) Each URL can be associated with any of several (or none) developments. You already have two of the tables you need, what you are missing is the association or relationship table. It is usually just a two column table but more columns can be added if you need to store information about the combination of a development to a virtual tour URL. I will make up a column that fits that last category and explain why it fits on this third table. Here is what your association table could look like: CREATE TABLE development_URL ( tbl_development_intDevID mediumint(9) not null, tbl_urldetails_intID int(11) not null, dateAvailable datetime null, PRIMARY KEY(tbl_development_intDevID, tbl_urldetails_intID) ) INSERT development_URL VALUES(50,7,'2004-11-01'); Let's say that property 50 also needs to show the URLs for tours 1,2, and 4 right now. INSERT development_URL VALUES (50,1,null), (50,2,null), (50,4,null) Shawn, I wrote this query using your relation table. select t1.tbl_development_intdevid,r1.* from development_url as t1 inner join tbl_urldetails as r1 on r1.intid = t1.tbl_urldetails_intid where tbl_development_intdevid='50'; Thanks very much for your help, I still struggle to think database, but my understanding will now (hopefully) leapfrog. It's always a struggle when you are at such a low level of your learning curve that you cannot easily formulate your pleas for 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]
Re: Understanding Joins : Baffled
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]
Re: Understanding Joins : Baffled
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
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
Re: Understanding Joins : Baffled
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
Re: Understanding Joins : Baffled
On Fri, 3 Sep 2004 13:06:45 -0400, wrote: 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 (lookup table) CREATE TABLE `tbl_urldetails` ( intID` int(11) NOT NULL auto_increment, `vtEhouseName` varchar(50) default NULL, `vtBarrattName` varchar(50) default NULL, `vtDescription` text, `vtInternalNote` varchar(50) default NULL, `vtHyperlink` varchar(50) default NULL, `dtmVTLastUpdate` timestamp(14) NOT NULL, `vtRandom` int(3) default NULL, PRIMARY KEY (`intID`)) TYPE=MyISAM CREATE TABLE `tbl_development` ( `intDevID` mediumint(9) unsigned NOT NULL auto_increment, `txtBarrattID` varchar(20) default NULL, `txtDevName` varchar(60) default NULL, `txtDevWebSiteName` varchar(80) default NULL, `dtmDevDate` datetime default NULL, `dtmDevDateLastUpdate` datetime default NULL, `txtDevDivision` varchar(30) default NULL, `txtDevAddress` text, `intDevStage` smallint(6) default NULL, `txtDevPostCode` varchar(20) default NULL, `txtDevCity` varchar(40) default NULL, `txtDevRegion` varchar(40) default NULL, `txtDevPropertyType` varchar(15) default NULL, `txtDevVirtualTourURL` varchar(60) default NULL, `txtDevVirtualTourURL1` varchar(60) default NULL, `txtDevVirtualTourURL2` varchar(60) default NULL, `txtDevVirtualTourURL3` varchar(60) default NULL, `txtDevVirtualTourURL4` varchar(60) default NULL, `txtDevVirtualTourURL5` varchar(60) default NULL, `txtDevVirtualTourURL6` varchar(60) default NULL, `txtDevVirtualTourURL7` varchar(60) default NULL, `txtDevVirtualTourURL8` varchar(60) default NULL, PRIMARY KEY (`intDevID`)) TYPE=MyISAM 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]
Re: Understanding Joins : Baffled
Let me see if I can summarize what I know so far: A. Any record in tbl_development can be associated with up to 8 URLs that represent virtual tours of the dwelling types in that particular development. B. tbl_urldetails is a table acting as a master list of the virtual tours you have ready to use. The records in tbl_development should reference these URLs. This is what we call a many-to-many relationship and is commonly modeled in a relational database with a 3-table structure. It is a many-to-many because: 1) Each development can associate itself to several (up to 8, you say) URLs 2) Each URL can be associated with any of several (or none) developments. You already have two of the tables you need, what you are missing is the association or relationship table. It is usually just a two column table but more columns can be added if you need to store information about the combination of a development to a virtual tour URL. I will make up a column that fits that last category and explain why it fits on this third table. Here is what your association table could look like: CREATE TABLE development_URL ( tbl_development_intDevID mediumint(9) not null, tbl_urldetails_intID int(11) not null, dateAvailable datetime null, PRIMARY KEY(tbl_development_intDevID, tbl_urldetails_intID) ) The first column gets an intDevID value that exists in tbl_development. The second column gets a value from the intID column of tbl_urldetails. The third column can be used by your website to automate the publishing of a virtual tour to your website. Because that is a date specific to the assignment of a tour to a development, that information belongs on this table. It makes no sense to put that date on either of the other two tables, does it? Lets say that development 50 will have the type of property that corresponds to URL 7 ready for touring as of 2004-11-01 but you, being rewarded for your months of hard labor, plan to vacation that week. Because you have included that date column in the association table you can set up the data now but because the date is a future date, your will decide to not to show it until that date comes around. Now you look like the true GURU because you can schedule a update to your site. Automation is our friend. INSERT development_URL VALUES(50,7,'2004-11-01'); Let's say that property 50 also needs to show the URLs for tours 1,2, and 4 right now. INSERT development_URL VALUES (50,1,null), (50,2,null), (50,4,null) I chose the null value for the dates as a signal for always on (they don't have a starting date...). Because the associations between a Development and its Virtual Tour URLs are being maintained in a separate table, you will no longer need the fields names txtDevVirtualTour## on your tbl_development. Did I help you at all or did I just muddy-up your situation? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine news [EMAIL PROTECTED] wrote on 09/03/2004 02:17:59 PM: On Fri, 3 Sep 2004 13:06:45 -0400, wrote: 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
