need help with relational tables/fields
Hey, i have a table called users which has my users in it, each have a uid field. I also have a stories table which has stories in it each with a sid field for each story but also a uid field so i know which user the story belongs to. i want to write a query that will display the story depending on the user.. basically i guess it works like a word press blog. eg, Andrew logs into the site and only his stories are displayed, but if john logs in, only his stories are displayed. Here are my tables mysql describe stories; +---+---+--+-+---++ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+---++ | sid | int(8)| NO | PRI | NULL | auto_increment | | uid | int(8)| NO | MUL | NULL || | story | text | NO | | NULL || | storyDATE | timestamp | NO | | CURRENT_TIMESTAMP || +---+---+--+-+---++ 4 rows in set (0.02 sec) mysql describe users; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | uid | int(8) | NO | PRI | NULL| auto_increment | | username | varchar(12) | NO | | NULL|| | password | varchar(32) | NO | | NULL|| | firstName | varchar(15) | NO | | NULL|| | lastName | varchar(15) | NO | | NULL|| +---+-+--+-+-++ 5 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with relational tables/fields
AndrewJames schrieb:
Hey,
i have a table called users which has my users in it, each have a uid
field.
I also have a stories table which has stories in it each with a sid field
for each story but also a uid field so i know which user the story
belongs
to.
i want to write a query that will display the story depending on the
user..
basically i guess it works like a word press blog. eg, Andrew logs
into the
site and only his stories are displayed, but if john logs in, only his
stories are displayed.
Here are my tables
mysql describe stories;
+---+---+--+-+---++
| Field | Type | Null | Key | Default |
Extra |
+---+---+--+-+---++
| sid | int(8)| NO | PRI | NULL |
auto_increment |
| uid | int(8)| NO | MUL | NULL
||
| story | text | NO | | NULL
||
| storyDATE | timestamp | NO | | CURRENT_TIMESTAMP
||
+---+---+--+-+---++
4 rows in set (0.02 sec)
mysql describe users;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| uid | int(8) | NO | PRI | NULL| auto_increment |
| username | varchar(12) | NO | | NULL||
| password | varchar(32) | NO | | NULL||
| firstName | varchar(15) | NO | | NULL||
| lastName | varchar(15) | NO | | NULL||
+---+-+--+-+-++
5 rows in set (0.01 sec)
I guess you include uid as part of your session, once a user has
authenticated successfully.
Since you have the mapping uid - sid already included in the
stories-table the query is very simple:
SELECT sid FROM stories WHERE uid = ${UID_OF_LOGGED_IN_USER}
cheers,
wolfgang
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with relational tables/fields
AndrewJames schrieb:
Hey,
i have a table called users which has my users in it, each have a uid
field.
I also have a stories table which has stories in it each with a sid field
for each story but also a uid field so i know which user the story
belongs
to.
i want to write a query that will display the story depending on the
user..
basically i guess it works like a word press blog. eg, Andrew logs
into the
site and only his stories are displayed, but if john logs in, only his
stories are displayed.
Here are my tables
mysql describe stories;
+---+---+--+-+---++
| Field | Type | Null | Key | Default |
Extra |
+---+---+--+-+---++
| sid | int(8)| NO | PRI | NULL |
auto_increment |
| uid | int(8)| NO | MUL | NULL
||
| story | text | NO | | NULL
||
| storyDATE | timestamp | NO | | CURRENT_TIMESTAMP
||
+---+---+--+-+---++
4 rows in set (0.02 sec)
mysql describe users;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| uid | int(8) | NO | PRI | NULL| auto_increment |
| username | varchar(12) | NO | | NULL||
| password | varchar(32) | NO | | NULL||
| firstName | varchar(15) | NO | | NULL||
| lastName | varchar(15) | NO | | NULL||
+---+-+--+-+-++
5 rows in set (0.01 sec)
I assume you include the uid as part of your session, once a user has
authenticated successfully.
Since you have the mapping uid - sid already included in the
stories-table the query is very simple:
SELECT sid FROM stories WHERE uid = ${UID_OF_LOGGED_IN_USER}
cheers,
wolfgang
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: need help with relational tables/fields
AndrewJames schrieb:
Hey,
i have a table called users which has my users in it, each have a uid
field.
I also have a stories table which has stories in it each with a sid field
for each story but also a uid field so i know which user the story
belongs
to.
i want to write a query that will display the story depending on the
user..
basically i guess it works like a word press blog. eg, Andrew logs
into the
site and only his stories are displayed, but if john logs in, only his
stories are displayed.
Here are my tables
mysql describe stories;
+---+---+--+-+---++
| Field | Type | Null | Key | Default |
Extra |
+---+---+--+-+---++
| sid | int(8)| NO | PRI | NULL |
auto_increment |
| uid | int(8)| NO | MUL | NULL
||
| story | text | NO | | NULL
||
| storyDATE | timestamp | NO | | CURRENT_TIMESTAMP
||
+---+---+--+-+---++
4 rows in set (0.02 sec)
mysql describe users;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| uid | int(8) | NO | PRI | NULL| auto_increment |
| username | varchar(12) | NO | | NULL||
| password | varchar(32) | NO | | NULL||
| firstName | varchar(15) | NO | | NULL||
| lastName | varchar(15) | NO | | NULL||
+---+-+--+-+-++
5 rows in set (0.01 sec)
I assume you include the uid as part of your session, once a user has
authenticated successfully.
Since you have the mapping uid - sid already included in the
stories-table the query is very simple:
SELECT sid FROM stories WHERE uid = ${UID_OF_LOGGED_IN_USER}
cheers,
wolfgang
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: relational tables
Sorry, I'm a top quoter. This is what I want to do. I'm still told there re problems with my keys. DROP TABLE IF EXISTS `person`; CREATE TABLE `person` ( `person_id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, `email` varchar(255) default NULL, PRIMARY KEY (`person_id`), KEY `email` (`email`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 1, 'Name', '[EMAIL PROTECTED]' ) ; INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 2, 'second Name', '[EMAIL PROTECTED]' ) ; DROP TABLE IF EXISTS `shopping`; CREATE TABLE IF NOT EXISTS `shopping` ( `shopping_id` int(11) NOT NULL, `email` varchar(255) default NULL, `name` varchar(255) default NULL, PRIMARY KEY (`shopping_id`), UNIQUE KEY `email` (`email`), UNIQUE KEY `name` (`name`), FOREIGN KEY (`email`) REFERENCES `person` (`email`), FOREIGN KEY (`name`) REFERENCES `person` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Sebastian Mendel wrote: This is InnoDB so I should be able to do this by SQL, right? where do you want to see this drop-downs? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: relational tables
See below... -Original Message- From: John Taylor-Johnston [mailto:John.Taylor- [EMAIL PROTECTED] Sent: Thursday, March 20, 2008 2:17 PM To: Sebastian Mendel; mysql@lists.mysql.com Subject: Re: relational tables DROP TABLE IF EXISTS `person`; CREATE TABLE `person` ( `person_id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, `email` varchar(255) default NULL, PRIMARY KEY (`person_id`), KEY `email` (`email`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 1, 'Name', '[EMAIL PROTECTED]' ) ; INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 2, 'second Name', '[EMAIL PROTECTED]' ) ; DROP TABLE IF EXISTS `shopping`; CREATE TABLE IF NOT EXISTS `shopping` ( `shopping_id` int(11) NOT NULL, `email` varchar(255) default NULL, `name` varchar(255) default NULL, PRIMARY KEY (`shopping_id`), UNIQUE KEY `email` (`email`), UNIQUE KEY `name` (`name`), FOREIGN KEY (`email`) REFERENCES `person` (`email`), FOREIGN KEY (`name`) REFERENCES `person` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Something doesn't smell right with the 'shopping' table. Rather than using what appears to be the same values (aside from the person_id as opposed to the shopping_id), wouldn't it be more effective to have the shopping table have 'shopping_id' and 'person_id' fields? With that approach, you could get away from having the same data in two tables. But, you'll have to start building ways to look up the person_id based on name and email values in order to populate the shopping table. Took a flyer at an updated version of the above (not knowing what your intent is): DROP TABLE IF EXISTS `person`; CREATE TABLE `person` ( `person_id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, `email` varchar(255) default NULL, PRIMARY KEY (`person_id`), KEY `email` (`email`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( null, 'Name', '[EMAIL PROTECTED]' ) ; -- updated the key value to allow for being created automatically INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( null, 'second Name', '[EMAIL PROTECTED]' ) ; -- same thing here DROP TABLE IF EXISTS `shopping`; CREATE TABLE IF NOT EXISTS `shopping` ( `shopping_id` int(11) NOT NULL, `person_id` int(11) NOT NULL, PRIMARY KEY (`shopping_id`), FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1; The syntax of the above might not be 100%...but it looks to be close enough. No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.21.7/1335 - Release Date: 3/19/2008 9:54 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
relational tables
I want to make a relational link from `data` to `shopping` so when I insert a new record in `shopping`, I will see the contents of `data`.`name` and `data`.`email` as drop-down menus in `shopping`. This is InnoDB so I should be able to do this by SQL, right? Thanks, John DROP TABLE IF EXISTS `data`; CREATE TABLE `data` ( `id` int(5) NOT NULL auto_increment, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `data` VALUES(1, 'Allen, Carolyn', '[EMAIL PROTECTED]'); INSERT INTO `data` VALUES(2, 'Atwood, Margaret', '[EMAIL PROTECTED]'); DROP TABLE IF EXISTS `shopping`; CREATE TABLE `shopping` ( `id` int(5) NOT NULL auto_increment, `name` varchar(100) NOT NULL, `address` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: relational tables
John Taylor-Johnston schrieb: I want to make a relational link from `data` to `shopping` so when I insert a new record in `shopping`, I will see the contents of `data`.`name` and `data`.`email` as drop-down menus in `shopping`. This is InnoDB so I should be able to do this by SQL, right? where do you want to see this drop-downs? MySQL is an database server, and i m not aware of any place where it would display any drop-downs, possible you speak of some sort of GUI, like MySQL Admin or phpMyAdmin? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query relational tables
* Dee I have a table that contains ids for several other tables. Setup is as followed: +--+--+--+-+-+ + | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+ + | id | int(11) | | PRI | NULL|auto_increment | | title| varchar(100) | YES | | NULL| | | rateID | int(10) unsigned | YES | | NULL| | | actorID | int(10) unsigned | YES | | NULL| | | actor2ID | int(10) unsigned | YES | | NULL| | | actor3ID | int(10) unsigned | YES | | NULL| | +--+--+--+-+-+ + +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | actorID | int(10) unsigned | | PRI | NULL| auto_increment | | first | varchar(20) | YES | | NULL|| | middle | varchar(20) | YES | | NULL|| | last| varchar(20) | YES | | NULL|| +-+--+--+-+-++ +--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | rateID | int(10) unsigned | | PRI | NULL| auto_increment | | rating | varchar(15) | YES | | NULL|| ++--+--+-+-++ How can I query the movie tables so that it will get the names of the actors when each actor field may have a different name or may be null? I am unable to query on more than one actor. Two things to remember: - When you need to join the same table multiple times in the same query, you have to use aliases for the table name. - When there could be no row found for a join, but you still want a result row, you need to use LEFT JOIN. Try this: SELECT title,a1.last,a2.last,a3.last FROM movie LEFT JOIN actor a1 ON a1.actorID = movie.actorId LEFT JOIN actor a2 ON a2.actorID = movie.actor2Id LEFT JOIN actor a3 ON a3.actorID = movie.actor3Id HTH, -- Roger - 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
Query relational tables
Hi, I have a table that contains ids for several other tables. Setup is as followed: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(11) | | PRI | NULL|auto_increment | | title| varchar(100) | YES | | NULL|| | rateID | int(10) unsigned | YES | | NULL|| | actorID | int(10) unsigned | YES | | NULL|| | actor2ID | int(10) unsigned | YES | | NULL|| | actor3ID | int(10) unsigned | YES | | NULL|| +--+--+--+-+-++ +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | actorID | int(10) unsigned | | PRI | NULL| auto_increment | | first | varchar(20) | YES | | NULL|| | middle | varchar(20) | YES | | NULL|| | last| varchar(20) | YES | | NULL|| +-+--+--+-+-++ +--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | rateID | int(10) unsigned | | PRI | NULL| auto_increment | | rating | varchar(15) | YES | | NULL|| ++--+--+-+-++ How can I query the movie tables so that it will get the names of the actors when each actor field may have a different name or may be null? I am unable to query on more than one actor. Thanks Dee - 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
relational tables query help
Hi all, I have three tables: partners (id,name,url,desc) services (id,desc,category) partner_service_rel (pid,sid) Each partner offers a range of services, and each services has an id, description, and category. Now, it's EASY to search for partners that offer a certain service, but I can't figure out a smart way to list all services for a specific partnerID, grouped by category. I got this far (which I think is perhaps WAY off track!!): SELECT services.*, partners.id FROM services, partner_service_rel WHERE partner_service_rel.pid='1' AND partners.id=partner_service_rel.sid And I'm getting Unknown table 'partners' in field list. Any advice warmly received :) Justin French sql,query - 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: relational tables query help
because there isn't any partners table in the FROM clause! --- Justin French [EMAIL PROTECTED] ha scritto: Hi all, I have three tables: partners (id,name,url,desc) services (id,desc,category) partner_service_rel (pid,sid) Each partner offers a range of services, and each services has an id, description, and category. Now, it's EASY to search for partners that offer a certain service, but I can't figure out a smart way to list all services for a specific partnerID, grouped by category. I got this far (which I think is perhaps WAY off track!!): SELECT services.*, partners.id FROM services, partner_service_rel WHERE partner_service_rel.pid='1' AND partners.id=partner_service_rel.sid And I'm getting Unknown table 'partners' in field list. Any advice warmly received :) Justin French sql,query - 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 __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - 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: relational tables
* Javier Gonzalo Gloria Medina How I crate this relational tables with mysql. Using the CREATE TABLE statement: URL: http://www.mysql.com/doc/C/R/CREATE_TABLE.html where the two tables must be related by id ok You combine the tables with the SELECT statement, you don't need to do anything special when you create the tables to tell mysql that the tables are related. URL: http://www.mysql.com/doc/S/E/SELECT.html -- Roger - 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
