Re: Is this query possible?

[Tanner Postert]

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?

[Brent Baisley]

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: mysql@lists.mysql.com
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?

[John Meyer]

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?

[Tanner Postert]

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...

[KH Chiu]

Yes. You can use INSERT INTO table 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 mysql-unsubscribe-
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Yours,
KH Chiu
CA 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...

[Jennifer Goodie]

Yes. You can use INSERT INTO table 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 table 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...

[Jonathan Villa]

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 table 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 mysql-unsubscribe-
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Yours,
KH Chiu
CA 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?

[Luc Foisy]

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

RE: is this query possible?

[Jay Blanchard]

[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?

[Erik Price]

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