Re: Is this query possible?
the query works, but i need to get the total number of songs on that CD, as well as the sum of the lengths of the songs on the CD... is that possible in 1 query? On 8/2/06, Tanner Postert <[EMAIL PROTECTED]> wrote: just to clarify to Brent, the songs lists the original artist and album. the CD table is for the information for a NEW mix CD. that CD contains the tracks listed in the tracks table, which point back to the individual songs. the reason the tracks are not listed in the songs table, is because they can be part of multiple CDs. one person could put that same song on tons of different mix CDs as wells as other users using it too, anyways, thanks for the help, i'll let you know how your suggestions faired. On 8/2/06, John Meyer <[EMAIL PROTECTED]> wrote: > > I've dealt with this in terms of Books and Titles. Those two are > separate: > one title can have many book editions published in it. Also, you can > have a > book with multiple titles (anthology, for instance). > I suppose it is possible for album not to be the same as cd title, > particularly if you have old vinyl albums around that you want to sell. > > -Original Message- > From: Brent Baisley [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 02, 2006 2:13 PM > To: Tanner Postert; mysql@lists.mysql.com > Subject: Re: Is this query possible? > > I'm not sure why you split out track, track is really kind of an > attribute > of a song. Especially since you have artist and album with the song. > Wouldn't album be the same as cd title? I'm not quite getting the logic > of > your schema. > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: Is this query possible?
just to clarify to Brent, the songs lists the original artist and album. the CD table is for the information for a NEW mix CD. that CD contains the tracks listed in the tracks table, which point back to the individual songs. the reason the tracks are not listed in the songs table, is because they can be part of multiple CDs. one person could put that same song on tons of different mix CDs as wells as other users using it too, anyways, thanks for the help, i'll let you know how your suggestions faired. On 8/2/06, John Meyer <[EMAIL PROTECTED]> wrote: I've dealt with this in terms of Books and Titles. Those two are separate: one title can have many book editions published in it. Also, you can have a book with multiple titles (anthology, for instance). I suppose it is possible for album not to be the same as cd title, particularly if you have old vinyl albums around that you want to sell. -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 2:13 PM To: Tanner Postert; mysql@lists.mysql.com Subject: Re: Is this query possible? I'm not sure why you split out track, track is really kind of an attribute of a song. Especially since you have artist and album with the song. Wouldn't album be the same as cd title? I'm not quite getting the logic of your schema. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is this query possible?
I've dealt with this in terms of Books and Titles. Those two are separate: one title can have many book editions published in it. Also, you can have a book with multiple titles (anthology, for instance). I suppose it is possible for album not to be the same as cd title, particularly if you have old vinyl albums around that you want to sell. -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 2:13 PM To: Tanner Postert; mysql@lists.mysql.com Subject: Re: Is this query possible? I'm not sure why you split out track, track is really kind of an attribute of a song. Especially since you have artist and album with the song. Wouldn't album be the same as cd title? I'm not quite getting the logic of your schema. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is this query possible?
I'm not sure why you split out track, track is really kind of an attribute of a song. Especially since you have artist and album with the song. Wouldn't album be the same as cd title? I'm not quite getting the logic of your schema. It would have been helpful if you provided your current query. Anyway, you want to find out what CDs contain a particular song. So start your query out by finding the song, then you need to find what tracks it's associated with, then what cd those tracks are associate with. Something like this should work: SELECT cd.title,song.title,track FROM song JOIN track ON song.id=song_id AND song.id='X' JOIN cd ON track.cd_id=cd.id Same query, worded slightly different: SELECT cd.title,song.title,track FROM song JOIN track ON song.id=song_id JOIN cd ON track.cd_id=cd.id WHERE song.id='X' - Original Message - From: "Tanner Postert" <[EMAIL PROTECTED]> To: Sent: Tuesday, August 01, 2006 8:21 PM Subject: Is this query possible? ok, here is the schema that I am working with: CREATE TABLE `cd` ( `id` int(10) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned NOT NULL, `title` varchar(100) NOT NULL, `description` text NOT NULL, `dt` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM CREATE TABLE `song` ( `id` int(10) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned NOT NULL, `title` varchar(50) NOT NULL, `artist` varchar(50) NULL, `album` varchar(50) NULL, `featuring` varchar(50) NULL, `length` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM CREATE TABLE `track` ( `id` int(10) unsigned NOT NULL auto_increment, `song_id` int(10) unsigned NOT NULL, `cd_id` int(10) unsigned NOT NULL, `track` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cd_id` (`cd_id`,`track`), UNIQUE KEY `song_id` (`song_id`,`cd_id`) ) ENGINE=MyISAM the query now, is pulling back all the cd table data, as well as the total length of the songs that are tracks on that CD, as well as the number of tracks. that query is working fine. my goal now, is to pull the same data, but only for CDs that contain a specific track. but if i add "where song.id = 'X' the the count only returns 1 and the sum only returns the length for that 1 song. I'd like the full length and track count, but only for CDs that contain a specific song. SELECT cd.*, count(track.track) as tracks, sum(song.length) as length from cd LEFT JOIN (track, song) on (track.cd_id = cd.id and track.song_id = song.id) GROUP BY cd.id ORDER BY dt DESC LIMIT 0,1 thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is this query possible...
Well, I thought I could figure it out but I guess I couldn't. This is exactly what I want to do... //Using Access now, but when I use MySQL, I can just get the last insert id. $itemID = $_POST['item_id']; $itemQty = $_POST['item_qty']; $itemPrice = $_POST['price']; $orderID = Will be a value taken from an auto_increment field in another table. //I'm using PHP and from I have read and have been told, there is no odbc function to get the last insert id, so I'm stuck doing this. INSERT INTO order_history (item_id, item_qty, item_price, order_id) VALUES ($itemID, $itemQty, $itemPrice, $orderID) {Is this where I would do a select and if so how?} So, as you see, I have a combination of values, 3 vars from a form submission, and one value already stored in the database. I know that I could run 2 queries, but that is what I am trying to avoid. Thanks in advance. ---> Jonathan -Original Message- From: KH Chiu [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 8:52 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Is this query possible... Yes. You can use INSERT INTO select from .. Regards, > All, > > Is this query possible to do: > > I am going to select an id from a table > > SELECT order_id FROM orders WHERE order_start = 1324 > > I will then use the order_id from this query and insert it as well as > some others values I have into another table. > > So in summary, I want to perform a SELECT and INSERT in the same > database query. > > Can I do it? > > If this matters, I will be doing this in 2 different applications. 1 > database is MySQL, and the other is MS Access. > > -jonathan > > - > 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 > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Yours, KH Chiu C&A Computer Consultants Ltd. Tel: 3104 2070 Fax: 3010 0896 Email: [EMAIL PROTECTED] Website: www.caconsultant.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: Is this query possible...
>Yes. You can use INSERT INTO select from .. >> If this matters, I will be doing this in 2 different applications. 1 >> database is MySQL, and the other is MS Access. Not if the two tables are on different database servers. But maybe I'm reading that wrong and the application is just being developed to run on two different instances. -Original Message- From: KH Chiu [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 6:52 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Is this query possible... Yes. You can use INSERT INTO select from .. Regards, > All, > > Is this query possible to do: > > I am going to select an id from a table > > SELECT order_id FROM orders WHERE order_start = 1324 > > I will then use the order_id from this query and insert it as well as > some others values I have into another table. > > So in summary, I want to perform a SELECT and INSERT in the same > database query. > > Can I do it? > > If this matters, I will be doing this in 2 different applications. 1 > database is MySQL, and the other is MS Access. > > -jonathan - 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: Is this query possible...
Yes. You can use INSERT INTO select from .. Regards, > All, > > Is this query possible to do: > > I am going to select an id from a table > > SELECT order_id FROM orders WHERE order_start = 1324 > > I will then use the order_id from this query and insert it as well as > some others values I have into another table. > > So in summary, I want to perform a SELECT and INSERT in the same > database query. > > Can I do it? > > If this matters, I will be doing this in 2 different applications. 1 > database is MySQL, and the other is MS Access. > > -jonathan > > - > 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 > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Yours, KH Chiu C&A Computer Consultants Ltd. Tel: 3104 2070 Fax: 3010 0896 Email: [EMAIL PROTECTED] Website: www.caconsultant.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: is this query possible?
Hey, that's exactly what I needed! And I can still use WHERE clauses to further limit my results. That's great, thank you! I have one question though. In this LEFT JOIN syntax, you used the following format: LEFT JOIN secondary_table ON primary_table.col = secondary_table.col Is this optimized? In the case of WHERE clauses, for instance, I always put the main (most-limiting) criteria on the right side of the equals sign and the uncertain (least-limiting) criteria on the left side of the equals sign. Since I've never used LEFT JOIN before, I am unsure of the best way to do it. Erik On Friday, June 14, 2002, at 04:05 PM, Luc Foisy wrote: > How bout > > SELECT main.id, sub1.other, sub2.other FROM main LEFT JOIN sub1 ON > main.sub1fk = sub1.id LEFT JOIN sub2 ON main.sub2fk = sub2.id > > Luc > mysql,sql > >> -Original Message- >> From: Erik Price [mailto:[EMAIL PROTECTED]] >> Sent: Friday, June 14, 2002 3:54 PM >> To: [EMAIL PROTECTED] >> Subject: is this query possible? >> >> >> I have a query that I have in mind, but am not sure of how I can >> actually write it. It might not even be possible. I was >> hoping someone >> could tell me if I will have to use two queries instead, or >> if this will >> actually work: >> >> (In simplified form:) >> >> ++ >> +---+| main | >> | sub1 |+++---+ >> +---+| id || sub2 | >> | id|---<| sub1fk |+---+ >> | other || sub2fk |>---| id| >> +---+++| other | >> +---+ >> >> As you can see from the simple diagram, I have a main table >> with its own >> primary key (id) but with two foreign key columns. The first one >> (sub1fk) points to the primary key of the table "sub1". The >> second one >> (sub2fk) points to the primary ky of the table "sub2". >> >> The query I'm trying to build would look something like this: >> >> SELECT main.id, >> IF(main.sub1fk,sub1.other,NULL) AS sub1other, >> IF(main.sub2fk,sub2.other,NULL) AS sub2other >> FROMmain, sub1, sub2 >> WHERE main.id = some_criteria_or_other >> AND sub1.id = main.sub1fk >> AND sub2.id = main.sub2fk; >> >> >> The above SQL, of course, won't work -- because there are no >> situations >> where all of the WHERE clauses are true. Rather, I'm trying to get a >> result set that would look like this (again, this is in theory): >> >> ++---+---+ >> | id | sub1other | sub2other | >> ++---+---+ >> | 1 | 2 | NULL | >> | 2 | NULL | 5 | >> | 3 | NULL |17 | >> | 4 | 8 | NULL | >> | .. |...etc |...etc | >> ++---+---+ >> >> Later, in my application, I can test each column for NULL and I will >> know that the other column is the one to use (for instance, >> if the value >> of the "sub1other" column is NULL in one record, then I'll >> use the value >> of sub2other to do what I want to do, and vice versa). >> >> But this just doesn't seem possible. I can always do it with two >> separate queries if need be, but it would be elegant to do it >> with one. >> Any advice? >> >> Thanks very much, >> >> Erik >> >> >> >> >> >> Erik Price >> Web Developer Temp >> Media Lab, H.H. Brown >> [EMAIL PROTECTED]> > > Erik Price Web Developer Temp Media Lab, H.H. Brown [EMAIL PROTECTED] - 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: is this query possible?
[snip] I have a query that I have in mind, but am not sure of how I can actually write it. It might not even be possible. I was hoping someone could tell me if I will have to use two queries instead, or if this will actually work: (In simplified form:) ++ +---+| main | | sub1 |+++---+ +---+| id || sub2 | | id|---<| sub1fk |+---+ | other || sub2fk |>---| id| +---+++| other | +---+ As you can see from the simple diagram, I have a main table with its own primary key (id) but with two foreign key columns. The first one (sub1fk) points to the primary key of the table "sub1". The second one (sub2fk) points to the primary ky of the table "sub2". . ++---+---+ | id | sub1other | sub2other | ++---+---+ | 1 | 2 | NULL | | 2 | NULL | 5 | | 3 | NULL |17 | | 4 | 8 | NULL | | .. |...etc |...etc | ++---+---+ Later, in my application, I can test each column for NULL and I will know that the other column is the one to use (for instance, if the value of the "sub1other" column is NULL in one record, then I'll use the value of sub2other to do what I want to do, and vice versa). [/snip] Erik, I don't have time to go into detail, but look into LEFT OUTER JOIN and RIGHT OUTER JOIN. You may be able to accomplish what you are trying to using these. I have done similar things in the past and was able to get the desired results. HTH! Jay sql, mysql, 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: is this query possible?
How bout SELECT main.id, sub1.other, sub2.other FROM main LEFT JOIN sub1 ON main.sub1fk = sub1.id LEFT JOIN sub2 ON main.sub2fk = sub2.id Luc mysql,sql > -Original Message- > From: Erik Price [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 14, 2002 3:54 PM > To: [EMAIL PROTECTED] > Subject: is this query possible? > > > I have a query that I have in mind, but am not sure of how I can > actually write it. It might not even be possible. I was > hoping someone > could tell me if I will have to use two queries instead, or > if this will > actually work: > > (In simplified form:) > > ++ > +---+| main | > | sub1 |+++---+ > +---+| id || sub2 | > | id|---<| sub1fk |+---+ > | other || sub2fk |>---| id| > +---+++| other | > +---+ > > As you can see from the simple diagram, I have a main table > with its own > primary key (id) but with two foreign key columns. The first one > (sub1fk) points to the primary key of the table "sub1". The > second one > (sub2fk) points to the primary ky of the table "sub2". > > The query I'm trying to build would look something like this: > > SELECT main.id, > IF(main.sub1fk,sub1.other,NULL) AS sub1other, > IF(main.sub2fk,sub2.other,NULL) AS sub2other > FROMmain, sub1, sub2 > WHERE main.id = some_criteria_or_other > AND sub1.id = main.sub1fk > AND sub2.id = main.sub2fk; > > > The above SQL, of course, won't work -- because there are no > situations > where all of the WHERE clauses are true. Rather, I'm trying to get a > result set that would look like this (again, this is in theory): > > ++---+---+ > | id | sub1other | sub2other | > ++---+---+ > | 1 | 2 | NULL | > | 2 | NULL | 5 | > | 3 | NULL |17 | > | 4 | 8 | NULL | > | .. |...etc |...etc | > ++---+---+ > > Later, in my application, I can test each column for NULL and I will > know that the other column is the one to use (for instance, > if the value > of the "sub1other" column is NULL in one record, then I'll > use the value > of sub2other to do what I want to do, and vice versa). > > But this just doesn't seem possible. I can always do it with two > separate queries if need be, but it would be elegant to do it > with one. > Any advice? > > Thanks very much, > > Erik > > > > > > Erik Price > Web Developer Temp > Media Lab, H.H. Brown > [EMAIL PROTECTED]> - 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