Newbie - How To Link Two Databases
Hi - I am new to MySQL SQL in general. I have been reading the documentation at www.mysql.com, but I've become quite confused. I hope that one of you can clear up a few issues for me. I have a database called parcel. The primary key in this database is called dxf, which contains a unique number corresponding to each record in the database. I have a second database called building. One of the columns in this database is called address. I need to be able to pull the data from this column when querying the parcel database. The problem I'm having is that while the building database does contain a column called dxf, in that database dxf isn't unique. To clarify, I may have multiple rows in the buildings database with the same entry for dxf. To clarify further... the parcel database contains information regarding land parcels. The building database contains information regarding buildings on these land parcels. dxf is the parcel id number. While you can only have one parcel of land, you may have multiple buildings on that parcel, each with a unique address. The parcel information is in the first database the address info is in the second database. In the first database, dxf is unique (as you can only have one land parcel). In the second database, dxf doesn't necessarily have to be unique as you may have multiple buildings on that one land parcel. I hope that made sense. The problem seems to be that, while each database contains a dxf entry, there really isn't a unique link between the two. Anyways... when I run a query on the parcel database, I need to be able to pull any address associated with that parcel from the building database. I can't quite figure out how to do this. Greatly appreciate any help! __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie - How To Link Two Databases
First, it appears that you are confusing database with table. A Database is a collection of tables. Each table has rows of data which is organized in columns. From what I understand, you have the following situation: Table: Parcels Columns: dxf (Primary Key, auto_number) [Description - used for illustration below] ...etc. columns for data about the parcels Table: Buildings Columns: [BuildingID (Primary Key)] dxf FOREIGN KEY REFERENCES Parcels(dxf) Address ...etc. If you have the parcel id (the dxf) already, you can list all the buildings on it by simply: SELECT Address FROM Buildings WHERE dxf = value of dxf If you have some other information about the parcel that you want to use as the identifier: SELECT P.Description, B.Address FROM Parcels as P INNER JOIN Buildings a B ON P.dxf = B.dxf WHERE P.Description = 'some description' Or similarly, if you have the Address, and you want to show the Parcel Description: SELECT P.Description, B.Address FROM Parcels as P INNER JOIN Buildings a B ON P.dxf = B.dxf WHERE B.Address = 'some address' HTH, Tore. - Original Message - From: Diver8 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 22, 2003 7:17 PM Subject: Newbie - How To Link Two Databases Hi - I am new to MySQL SQL in general. I have been reading the documentation at www.mysql.com, but I've become quite confused. I hope that one of you can clear up a few issues for me. I have a database called parcel. The primary key in this database is called dxf, which contains a unique number corresponding to each record in the database. I have a second database called building. One of the columns in this database is called address. I need to be able to pull the data from this column when querying the parcel database. The problem I'm having is that while the building database does contain a column called dxf, in that database dxf isn't unique. To clarify, I may have multiple rows in the buildings database with the same entry for dxf. To clarify further... the parcel database contains information regarding land parcels. The building database contains information regarding buildings on these land parcels. dxf is the parcel id number. While you can only have one parcel of land, you may have multiple buildings on that parcel, each with a unique address. The parcel information is in the first database the address info is in the second database. In the first database, dxf is unique (as you can only have one land parcel). In the second database, dxf doesn't necessarily have to be unique as you may have multiple buildings on that one land parcel. I hope that made sense. The problem seems to be that, while each database contains a dxf entry, there really isn't a unique link between the two. Anyways... when I run a query on the parcel database, I need to be able to pull any address associated with that parcel from the building database. I can't quite figure out how to do this. Greatly appreciate any help! __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie - How To Link Two Databases
Thanks for the reply. I'll try your suggestions. Actually, I *do* have two unique databases. They're originally FoxPro dbase files, each generated by a different application which I'm converting to MySQL for a php app. It won't be a problem to create a table for the buildings in the parcel database, hadn't even thought about doing that. Thanks again! --- Tore Bostrup [EMAIL PROTECTED] wrote: First, it appears that you are confusing database with table. A Database is a collection of tables. Each table has rows of data which is organized in columns. From what I understand, you have the following situation: Table: Parcels Columns: dxf (Primary Key, auto_number) [Description - used for illustration below] ...etc. columns for data about the parcels Table: Buildings Columns: [BuildingID (Primary Key)] dxf FOREIGN KEY REFERENCES Parcels(dxf) Address ...etc. If you have the parcel id (the dxf) already, you can list all the buildings on it by simply: SELECT Address FROM Buildings WHERE dxf = value of dxf If you have some other information about the parcel that you want to use as the identifier: SELECT P.Description, B.Address FROM Parcels as P INNER JOIN Buildings a B ON P.dxf = B.dxf WHERE P.Description = 'some description' Or similarly, if you have the Address, and you want to show the Parcel Description: SELECT P.Description, B.Address FROM Parcels as P INNER JOIN Buildings a B ON P.dxf = B.dxf WHERE B.Address = 'some address' HTH, Tore. - Original Message - From: Diver8 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 22, 2003 7:17 PM Subject: Newbie - How To Link Two Databases Hi - I am new to MySQL SQL in general. I have been reading the documentation at www.mysql.com, but I've become quite confused. I hope that one of you can clear up a few issues for me. I have a database called parcel. The primary key in this database is called dxf, which contains a unique number corresponding to each record in the database. I have a second database called building. One of the columns in this database is called address. I need to be able to pull the data from this column when querying the parcel database. The problem I'm having is that while the building database does contain a column called dxf, in that database dxf isn't unique. To clarify, I may have multiple rows in the buildings database with the same entry for dxf. To clarify further... the parcel database contains information regarding land parcels. The building database contains information regarding buildings on these land parcels. dxf is the parcel id number. While you can only have one parcel of land, you may have multiple buildings on that parcel, each with a unique address. The parcel information is in the first database the address info is in the second database. In the first database, dxf is unique (as you can only have one land parcel). In the second database, dxf doesn't necessarily have to be unique as you may have multiple buildings on that one land parcel. I hope that made sense. The problem seems to be that, while each database contains a dxf entry, there really isn't a unique link between the two. Anyways... when I run a query on the parcel database, I need to be able to pull any address associated with that parcel from the building database. I can't quite figure out how to do this. Greatly appreciate any help! __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble
