Re: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
Hi. On Friday 18 May 2012 18:21:07 Daevid Vincent wrote: Actually, I may have figured it out. Is there a better way to do this? I don't see why you need the dvds table when the dvd_id is in the scene table: SELECT a.dvd_id FROM scenes_list a, moviefiles b WHERE a.scene_id = b.scene_id AND b.format_id = '13'; or am I misunderstanding something? Cheers, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
There are a bunch of other columns in all these tables. A quick reason is need the dvd.title too therefore the dvd table is needed. Another reason is that the query is generated programmatically based upon parameters passed to a method. But yes, I do she your point and maybe I can refactor some things in this special case. I haven't tried your query as I'm home and not at work right ATM, but I think you need a DISTINCT dvd_id right? Otherwise I'll get a bunch of rows all with the same dvd_id since multiple scene_ids will match. d -Original Message- From: Mark Kelly [mailto:my...@wastedtimes.net] Sent: Saturday, May 19, 2012 3:34 PM To: mysql@lists.mysql.com Subject: Re: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format Hi. On Friday 18 May 2012 18:21:07 Daevid Vincent wrote: Actually, I may have figured it out. Is there a better way to do this? I don't see why you need the dvds table when the dvd_id is in the scene table: SELECT a.dvd_id FROM scenes_list a, moviefiles b WHERE a.scene_id = b.scene_id AND b.format_id = '13'; or am I misunderstanding something? Cheers, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
I would work from the inside out. What you're doing is grouping scenes by DVD and throwing away the ones that have no scenes. If you start with DVDs and do a subquery for each row, you'll process DVDs without scenes and then filter them out. If you start with a subquery that's grouped by DVD ID, alias it with an AS clause, and then join from that into the other tables, you can avoid that. It requires a little backwards-thinking but it tends to work well in a lot of cases. It would look something like this. Here's the query against the scenes: select dvd_id, count(*) as cnt from scenes_list group by dvd_id having count(*) 0; Now you can put that into a subquery and join to it: select ... from ( copy/paste the above ) as s_sl inner join dvds using (dvd_id) rest of query; I'm taking shortcuts because you said there is more to this query than you've shown us, so I won't spend the time to make it a complete query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
I have a table of DVDs, another of scenes and a last one of encoding formats/files... I want to find in one query all the dvd_id that have 0 scene_id that's encoded in format_id = 13. In other words all DVDs that are format_id = 13 despite not having a direct link. CREATE TABLE `dvds` ( `dvd_id` smallint(6) unsigned NOT NULL auto_increment, `dvd_title` varchar(64) NOT NULL default '', `description` text NOT NULL, PRIMARY KEY (`dvd_id`), ) CREATE TABLE `scenes_list` ( `scene_id` int(11) NOT NULL auto_increment, `dvd_id` int(11) NOT NULL default '0', `description` text NOT NULL, PRIMARY KEY (`scene_id`), ) CREATE TABLE `moviefiles` ( `scene_id` int(11) NOT NULL default '0', `format_id` int(3) NOT NULL default '0', `filename` varchar(255), `volume` smallint(6) NOT NULL default '0', PRIMARY KEY (`scene_id`,`format_id`), ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
-Original Message- Sent: Friday, May 18, 2012 5:34 PM I have a table of DVDs, another of scenes and a last one of encoding formats/files... I want to find in one query all the dvd_id that have 0 scene_id that's encoded in format_id = 13. In other words all DVDs that are format_id = 13 despite not having a direct link. CREATE TABLE `dvds` ( `dvd_id` smallint(6) unsigned NOT NULL auto_increment, `dvd_title` varchar(64) NOT NULL default '', `description` text NOT NULL, PRIMARY KEY (`dvd_id`), ) CREATE TABLE `scenes_list` ( `scene_id` int(11) NOT NULL auto_increment, `dvd_id` int(11) NOT NULL default '0', `description` text NOT NULL, PRIMARY KEY (`scene_id`), ) CREATE TABLE `moviefiles` ( `scene_id` int(11) NOT NULL default '0', `format_id` int(3) NOT NULL default '0', `filename` varchar(255), `volume` smallint(6) NOT NULL default '0', PRIMARY KEY (`scene_id`,`format_id`), ) Actually, I may have figured it out. Is there a better way to do this? SELECT DISTINCT d.`dvd_id` AS `id`, (SELECT COUNT(s_sl.scene_id) AS s_tally FROM scenes_list AS s_sl JOIN moviefiles AS s_mf USING (scene_id) WHERE s_sl.dvd_id = d.`dvd_id` AND s_mf.format_id = 13) AS s_tally FROM `dvds` AS d WHEREd.`date_release` = '2012-05-18' HAVING s_tally 0 ORDER BY d.`date_release` DESC; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Basic SQL Query Help Needed
I have a basic invoice table with related line items table Goal :I'd like to get ALL the related line items - for ALL the 'open' invoices... -- this should get a list of open (unpaid) invoices $query_invoice = SELECT DISTINCT ID from invoices where status = 'open' - -- then I'd like to get ALL the line items - in ALL these 'open' invoices - so how do I write the next SQL statement : $query_items = ??? SELECT ID, NAME from lineitems where --xx??? xx-- ??? Thanks, c...@hosting4days.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Basic SQL Query Help Needed
SELECT * FROM ORDER o INNER JOIN ORDER_LINE_ITEMS o_l ON (o.id=o_l.id) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. To: mysql@lists.mysql.com From: c...@hosting4days.com Subject: Basic SQL Query Help Needed Date: Tue, 25 Aug 2009 16:21:45 -0700 I have a basic invoice table with related line items table Goal :I'd like to get ALL the related line items - for ALL the 'open' invoices... -- this should get a list of open (unpaid) invoices $query_invoice = SELECT DISTINCT ID from invoices where status = 'open' - -- then I'd like to get ALL the line items - in ALL these 'open' invoices - so how do I write the next SQL statement : $query_items = ??? SELECT ID, NAME from lineitems where --xx??? xx-- ??? Thanks, c...@hosting4days.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ With Windows Live, you can organize, edit, and share your photos. http://www.windowslive.com/Desktop/PhotoGallery
SQL Query help
Friends, I am developing a database for accounting software. I have one problem regarding calculation of balances on daily basis for all ledgers. I am using Access 2003 as frontend. While designing I found that maintaining of daily balances is impossible to client's requirements. But as the solution I to execute two SQL queries for 365 times to calculate Opening and closing balances. what i need is a hint/example to write a function/SQL statement to run these queries in single/minimum iterations. table format: LedgerID | Opening Credit | Opening Debit | Current Credit | Current Debit | Closing Credit | Closing Debit | Date Previous dates closing balance should be the opening for next date. Please suggest the answer. Thanks, CPK
SQL Query help
This is probably tediously basic for all you super whiz MySQL people but help me out if you can. I have 2 tables in my database (there will be more) table_Applics table_keywords I want to select columns of information from table_applics based on the ID results from table_keywords. something like this I guess, Select ID From Keywords Where markets = 'Financial' This then gives me a list of ID's which I then want to take to table_applics and get the row of information for each ID number in the list that exist Select ID,NAME,LNAME,ADDRESS1 from table_applics Whats the best way to achieve this in a single query ? can any one help me with the Logic !!! Here from you soon I hope, Best regards Andy Fletcher
Re: SQL Query help
This is probably tediously basic for all you super whiz MySQL people but help me out if you can. I have 2 tables in my database (there will be more) table_Applics table_keywords I want to select columns of information from table_applics based on the ID results from table_keywords. something like this I guess, Select ID From Keywords Where markets = 'Financial' This then gives me a list of ID's which I then want to take to table_applics and get the row of information for each ID number in the list that exist Select ID,NAME,LNAME,ADDRESS1 from table_applics Whats the best way to achieve this in a single query ? can any one help me with the Logic !!! Here from you soon I hope, Best regards Andy Fletcher -- You can try that: I do not know if it is what you are looking for: SELECT - FROM TABLE1 INNER JOIN TABLE2 USING (common_column) GROUP BY -- ORDER BY ; Another way: SELECT - FROM TABLE1 INNER JOIN TABLE2 ON table1.field=table2.field (field as common_column) GROUP BY -- ORDER BY ; --- I hope that it works. Marcelo Araujo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL query help required
Hi, Background: I run a prediction league for soccer competitions. For every game, I want to show how many predictions were submitted per scoreline, ie. 10% said 1-0 20% said 0-1 30% said 2-3 etc. My prediction table has a predictionA and predicionB column with the submitted scores. Currently I do something like SELECT MAX(predictionA), MAX(predictionB) and then check for all the possible scorelines... if I got 2 and 3, then the possibilities would be: 2-0, 2-1, 2-2, 2-3 1-0, 1-1, 1-2, 1-3 0-0, 0-1, 0-2, 0-3 If the count for any predictions is 0 (ie no-one prediction that score), it gets omitted rather than saying 0%. These I do with individual selects in a code for loop. Is there a better way to do this with less calls to the database? Thanks! __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query help required
- Original Message - From: Riaan Oberholzer [EMAIL PROTECTED] 2-0, 2-1, 2-2, 2-3 1-0, 1-1, 1-2, 1-3 0-0, 0-1, 0-2, 0-3 SELECT CONCAT(predictionA, '-', predictionB) AS score, COUNT(CONCAT(predictionA, '-', predictionB)) AS count FROM table WHERE CONCAT(predictionA, '-', predictionB) 0 GROUP BY score ORDER BY predictionA DESC, predictionB Maybe not the fastest solution, but it is a single query ! The only thing you have to add is that you calculate the grandtotal to display the n% has selected.. part Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL query help
Hi, I have this table where the columns and rows are organized like this: +--+--+ | a| b| +--+--+ |1 |1 | |2 |1 | |2 |2 | |2 |3 | |2 |4 | |3 |1 | |3 |2 | +--+--+ I want to run a select that gives me one row for each unique value of 'a'. And in the cases where several rows exists for one single value of 'a', I require the rows with the largest values of 'b'. In SQL lingo that would be DISTINCT A and MAX(B), but I dont know how to write this into one select... I.e. if I would apply these rules on the set above, I should get the following result: +--+--+ | a| b| +--+--+ |1 |1 | |2 |4 | |3 |2 | +--+--+ Does anyone know how to formalize this request into a single SELECT statement (using mysql 4.0.13), please? Regards, Svein Seldal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query help
Hi, I forgot to mention that the table contains more information, it has more columns than just a and b. These extra columns contains the actual information that I'm looking for. I.e. the mentioned table could be looking like this: +--+--+--+--+-+--- | a| b| data | user | comment | ... +--+--+--+--+-+--- And I still want those entire rows with DISTINCT A and MAX(B). Regards Svein I have this table where the columns and rows are organized like this: +--+--+ | a| b| +--+--+ |1 |1 | |2 |1 | |2 |2 | |2 |3 | |2 |4 | |3 |1 | |3 |2 | +--+--+ I want to run a select that gives me one row for each unique value of 'a'. And in the cases where several rows exists for one single value of 'a', I require the rows with the largest values of 'b'. In SQL lingo that would be DISTINCT A and MAX(B), but I dont know how to write this into one select... I.e. if I would apply these rules on the set above, I should get the following result: +--+--+ | a| b| +--+--+ |1 |1 | |2 |4 | |3 |2 | +--+--+ Does anyone know how to formalize this request into a single SELECT statement (using mysql 4.0.13), please? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: A little SQL query help
Hi Ivan, depending on mark's needs (orginal poster of the question), you might be right. So far I have not so much experience with PHP. Best regards Nils Valentin Tokyo/Japan 2003 7 27 15:43Ivan Cukic : Nils How about LIKE \$%searchdata%\ ? The % sign should be in front of $ LIKE \%$searchdata%\ Ivan __ One World, one Web, one Program -- Microsoft promotional ad Ein Volk, ein Reich, ein Fuhrer -- Adolf Hitler __ http://alas.matf.bg.ac.yu/~mr02014 ___ _ _ _ __ ___ _ / __/___ __ | __| _ _ ___ \ / _/ / . / _\/\ | _| \ \/ / ._\ Ivan Cukic, Form Eye 2003. \ /_/ /___/_/ /_/_/_/ |___|_\ /\___ web development and design / __ / _ _ __ ___ / -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A little SQL query help
- Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Obantec Support [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, July 27, 2003 2:05 AM Subject: Re: A little SQL query help Hi Mark, 2003 7 27 06:09Obantec Support : Hi I have a database supplied to me that was excel but now uploaded to mysql. (the data is out of my hands.) i need to search for 1 of 2 categories against 3 fields with user inputted data. example Categories=Lessor , searchtype = BusinessCity searchdata = (user input) $sql = SELECT * FROM Contacts WHERE Categories=\$calltype\ and $searchtype LIKE \$searchdata%\; there are 2 Categories supplied via 2 different pages which load the same form and use call to determine Category. $searchtype is 1 of 3 text boxes against which searchdata is passed. I need to get all columns hence the * but need $searchdata to try and match all of text in the $searchtype column. using the LIKE \$searchdata%\ gets me only from start of test i.e. Fin will find Financial from Financial Text Widgets but not Financial from Widgets Text Finance How about LIKE \$%searchdata%\ ? I am not sure if you really need the $ there. If it is for php than it might be o.k there, but for MySQL I believe you don't need it in your case (if understood correctly what you want to do ;-) So please try also this : LIKE '%searchdata%' Best regards Nils Valentin Tokyo/Japan using php as my chosen language. Mark -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils Hi I was sure i tried the %string%. I need the $ since $searchdata is a php variable. \%$searchdata%\ works just fine. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A little SQL query help
Hi Mark, Thanks for the reply. Looks like my next step should be to learn PhP ;-) Best regards Nils Valentin Tokyo/Japan 2003 7 27 16:35Obantec Support : - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Obantec Support [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, July 27, 2003 2:05 AM Subject: Re: A little SQL query help Hi Mark, 2003 7 27 06:09Obantec Support : Hi I have a database supplied to me that was excel but now uploaded to mysql. (the data is out of my hands.) i need to search for 1 of 2 categories against 3 fields with user inputted data. example Categories=Lessor , searchtype = BusinessCity searchdata = (user input) $sql = SELECT * FROM Contacts WHERE Categories=\$calltype\ and $searchtype LIKE \$searchdata%\; there are 2 Categories supplied via 2 different pages which load the same form and use call to determine Category. $searchtype is 1 of 3 text boxes against which searchdata is passed. I need to get all columns hence the * but need $searchdata to try and match all of text in the $searchtype column. using the LIKE \$searchdata%\ gets me only from start of test i.e. Fin will find Financial from Financial Text Widgets but not Financial from Widgets Text Finance How about LIKE \$%searchdata%\ ? I am not sure if you really need the $ there. If it is for php than it might be o.k there, but for MySQL I believe you don't need it in your case (if understood correctly what you want to do ;-) So please try also this : LIKE '%searchdata%' Best regards Nils Valentin Tokyo/Japan using php as my chosen language. Mark -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils Hi I was sure i tried the %string%. I need the $ since $searchdata is a php variable. \%$searchdata%\ works just fine. Mark -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A little SQL query help
Hi I have a database supplied to me that was excel but now uploaded to mysql. (the data is out of my hands.) i need to search for 1 of 2 categories against 3 fields with user inputted data. example Categories=Lessor , searchtype = BusinessCity searchdata = (user input) $sql = SELECT * FROM Contacts WHERE Categories=\$calltype\ and $searchtype LIKE \$searchdata%\; there are 2 Categories supplied via 2 different pages which load the same form and use call to determine Category. $searchtype is 1 of 3 text boxes against which searchdata is passed. I need to get all columns hence the * but need $searchdata to try and match all of text in the $searchtype column. using the LIKE \$searchdata%\ gets me only from start of test i.e. Fin will find Financial from Financial Text Widgets but not Financial from Widgets Text Finance using php as my chosen language. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A little SQL query help
Hi Mark, 2003 7 27 06:09Obantec Support : Hi I have a database supplied to me that was excel but now uploaded to mysql. (the data is out of my hands.) i need to search for 1 of 2 categories against 3 fields with user inputted data. example Categories=Lessor , searchtype = BusinessCity searchdata = (user input) $sql = SELECT * FROM Contacts WHERE Categories=\$calltype\ and $searchtype LIKE \$searchdata%\; there are 2 Categories supplied via 2 different pages which load the same form and use call to determine Category. $searchtype is 1 of 3 text boxes against which searchdata is passed. I need to get all columns hence the * but need $searchdata to try and match all of text in the $searchtype column. using the LIKE \$searchdata%\ gets me only from start of test i.e. Fin will find Financial from Financial Text Widgets but not Financial from Widgets Text Finance How about LIKE \$%searchdata%\ ? I am not sure if you really need the $ there. If it is for php than it might be o.k there, but for MySQL I believe you don't need it in your case (if understood correctly what you want to do ;-) So please try also this : LIKE '%searchdata%' Best regards Nils Valentin Tokyo/Japan using php as my chosen language. Mark -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: MySQL sql query help
Hi Bruce, Thanks for your reply. I just got the time to take a look at it, and it makes sense. However, for the first test: SELECT distinct t.name FROM Teacher t INNER JOIN (TeacherClass tc2 INNER JOIN Class c2 ON tc2.classid = c2.id AND c2.name = 'English') ON t.teacherid = tc2.teacherid ; I'm getting: ERROR 1064: You have an error in your SQL syntax near '(TeacherClass tc2 INNER JOIN Class c2 ON tc2. classid = c2.id AND c2.name = 'Eng' at line 3; Seems MySQL doesn't much like having nested joins in joins... However, your principles led me to my result. As the search page will pass the class ids in anyway, I don't need to read these from the table. I.E. I pass the value 1 to the search script, not Math. Here's the working version in psuedo code: $sqlquery = select distinct t.name from teacher t ; for each required subject $sqlquery .= inner join teacherclass tc$counter . on t.id=tc$counter.teacherid . and tc$counter.classid=$subjectid ; $counter++; } $whereclause=; for each excluded subject $sqlquery .= left join teacherclass tc$counter . on t.id=tc$counter.teacherid . and tc$counter.classid=$subjectid ; $whereclause .= ($whereclause==) ? where : and ; $whereclause .= tc$counter.classid is null ; $counter++; } So a search with has taught 1, 3 6, and has not taught others: select distinct t.name from teacher t inner join teacherclass tc1 on t.id=tc1.teacherid and tc1.classid=1 left join teacherclass tc2 on t.id=tc2.teacherid and tc2.classid=2 left join teacherclass tc3 on t.id=tc3.teacherid and tc3.classid=3 inner join teacherclass tc4 on t.id=tc4.teacherid and tc4.classid=4 left join teacherclass tc5 on t.id=tc5.teacherid and tc5.classid=5 inner join teacherclass tc6 on t.id=tc6.teacherid and tc6.classid=6 where tc2.classid is null and tc3.classid is null and tc5.classid is null ; Remove any joins if you don't care either way if the teacher taught that subject. Switch inner to left and vice versa to change condition, and each left join should have a where is null clause. Thanks again Bruce, my favourite type of help, a pointer but allow me to learn why it works :-) I'm sure you knew most of the above, but I thought I'd included it all for the benefit of others on the list/anyone searching archives who may need this. Best of all, I think I'm a little closer to understanding joins! Rgds, Ben Balbo In response to your mail sent on Saturday, March 08, 2003 at 2:18:34 AM. Ben Balbo wrote: Teacher (id, name, ...) TeacherClass (teacherid, classid, timestamp) Class (id, name, ...) Now, what I'd really like to do it find all teachers who, for example, taught Engligh and Math, but not Biology, Here's a general, recursive, untested solution for you to play with. We start with a query that returns a list of all teachers. Don't worry about the DISTINCT or WHERE clause; it's used to extend the idea to the recursion. SELECT distinct t.name FROM Teachers t WHERE 1 = 1 ; If you want to AND in a condition on teaching a class, for instance English, put parentheses around the old joins and (substituting an appropriate counter for '2') add: INNER JOIN (TeacherClass tc2 INNER JOIN Class c2 ON tc2.classid = c2.classid AND c2.name = 'English') ON t.teacherid = tc2.teacherid If you want to AND in a condition on NOT teaching a class, for instance Biology, put parentheses around the old joins and (substituting an appropriate counter for '3') add: LEFT JOIN (TeacherClass tc3 INNER JOIN Class c3 ON tc3.classid = c3.classid AND c3.name = 'Biology') ON t.teacherid = tc3.teacherid and add the following to the WHERE clause: AND tc3.classid IS NULL Give it a try and let me know if it works! Bruce Feist - 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: MySQL sql query help
Ben Balbo wrote: Seems MySQL doesn't much like having nested joins in joins... Oops! Sorry about that. (Curse me for a MySQL novice! g) However, your principles led me to my result. As the search page will pass the class ids in anyway, I don't need to read these from the table. I.E. I pass the value 1 to the search script, not Math. OK, good. That would be more efficient, even if my way *had* worked. Thanks again Bruce, my favourite type of help, a pointer but allow me to learn why it works :-) I'm glad you liked it! It was an interesting question. Best of all, I think I'm a little closer to understanding joins! Excellent! Bruce Feist - 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
MySQL sql query help
Apologies if you now get this three times now, but I haven't received any copies through the mailing list yet, and the first 2 were sent 3 and 4 hours ago. I know email can go walkies for a few hours, but I've not been experiencing any other mail problems, either personally or in receiving MySQL mailings, so I thought I'd send it again in the hope it gets through, and that your a nice enough group of people to not get mad at duplicate postings :-) Ben -- Hi all, I've been trying to wrap my head round this problem for a few days now, and have plenty of pieces of scrap paper with lines and arrows and numbers to prove it. Anyway, here's the scenario: I have a table of teachers, and a table of classes. Any teacher can have taught any class, and any class can be taught by any teacher. Each lesson taken has a timestamp (so a teacher can teach a class more than once). Teacher (id, name, ...) TeacherClass (teacherid, classid, timestamp) Class (id, name, ...) Teachers: (1, Bob) (2, Kate) (3, Mike) Classes (1, Math) (2, English) (3, Biology) (4, Chemistry) (5, Physics) (6, Sport) Easy enough. Okay, so Bob has taught Math, Kate taught Math and Biology, and Mike taught everything bar Biology. I won't bother showing the TacherClass table contents. So, now I want to do a search on certain circumstances. I can search for teachers who taught Math, that's easy. I can even search for those who didn't teach Engligh: select t.name from teacher t left join teacherclass tc on t.id=tc.techerid and tc.classid=2 where tc.teacherid is null; Now, what I'd really like to do it find all teachers who, for example, taught Engligh and Math, but not Biology, and I don't care either way about the other classes. This would return Mike, but the actual SQL is baffling me. If I understand correctly, I'm going to have to join (probably left) the teacherclass table x-1 times where x is the number of constraints. So in my example above, I'll need 2 joins to get a column for class=2, class=1 and class!=3. Has any one got any pointers, hints, advice, solutions, links to online resources, etc, that could help me? Many thanks in advance... Ben - 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
MySQL sql query help
Hi all, I've been trying to wrap my head round this problem for a few days now, and have plenty of pieces of scrap paper with lines and arrows and numbers to prove it. Anyway, here's the scenario: I have a table of teachers, and a table of classes. Any teacher can have taught any class, and any class can be taught by any teacher. Each lesson taken has a timestamp (so a teacher can teach a class more than once). Teacher (id, name, ...) TeacherClass (teacherid, classid, timestamp) Class (id, name, ...) Teachers: (1, Bob) (2, Kate) (3, Mike) Classes (1, Math) (2, English) (3, Biology) (4, Chemistry) (5, Physics) (6, Sport) Easy enough. Okay, so Bob has taught Math, Kate taught Math and Biology, and Mike taught everything bar Biology. I won't bother showing the TacherClass table contents. So, now I want to do a search on certain circumstances. I can search for teachers who taught Math, that's easy. I can even search for those who didn't teach Engligh: select t.name from teacher t left join teacherclass tc on t.id=tc.techerid and tc.classid=2 where tc.teacherid is null; Now, what I'd really like to do it find all teachers who, for example, taught Engligh and Math, but not Biology, and I don't care either way about the other classes. This would return Mike, but the actual SQL is baffling me. If I understand correctly, I'm going to have to join (probably left) the teacherclass table x-1 times where x is the number of constraints. So in my example above, I'll need 2 joins to get a column for class=2, class=1 and class!=3. Has any one got any pointers, hints, advice, solutions, links to online resources, etc, that could help me? Many thanks in advance... Ben - 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
MySQL sql query help
Hi all, I've been trying to wrap my head round this problem for a few days now, and have plenty of pieces of scrap paper with lines and arrows and numbers to prove it. Anyway, here's the scenario: I have a table of teachers, and a table of classes. Any teacher can have taught any class, and any class can be taught by any teacher. Each lesson taken has a timestamp (so a teacher can teach a class more than once). Teacher (id, name, ...) TeacherClass (teacherid, classid, timestamp) Class (id, name, ...) Teachers: (1, Bob) (2, Kate) (3, Mike) Classes (1, Math) (2, English) (3, Biology) (4, Chemistry) (5, Physics) (6, Sport) Easy enough. Okay, so Bob has taught Math, Kate taught Math and Biology, and Mike taught everything bar Biology. I won't bother showing the TacherClass table contents. So, now I want to do a search on certain circumstances. I can search for teachers who taught Math, that's easy. I can even search for those who didn't teach Engligh: select t.name from teacher t left join teacherclass tc on t.id=tc.techerid and tc.classid=2 where tc.teacherid is null; Now, what I'd really like to do it find all teachers who, for example, taught Engligh and Math, but not Biology, and I don't care either way about the other classes. This would return Mike, but the actual SQL is baffling me. If I understand correctly, I'm going to have to join (probably left) the teacherclass table x-1 times where x is the number of constraints. So in my example above, I'll need 2 joins to get a column for class=2, class=1 and class!=3. Has any one got any pointers, hints, advice, solutions, links to online resources, etc, that could help me? Many thanks in advance... Ben - 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: MySQL sql query help
Ben Balbo wrote: Teacher (id, name, ...) TeacherClass (teacherid, classid, timestamp) Class (id, name, ...) Now, what I'd really like to do it find all teachers who, for example, taught Engligh and Math, but not Biology, Here's a general, recursive, untested solution for you to play with. We start with a query that returns a list of all teachers. Don't worry about the DISTINCT or WHERE clause; it's used to extend the idea to the recursion. SELECT distinct t.name FROM Teachers t WHERE 1 = 1 ; If you want to AND in a condition on teaching a class, for instance English, put parentheses around the old joins and (substituting an appropriate counter for '2') add: INNER JOIN (TeacherClass tc2 INNER JOIN Class c2 ON tc2.classid = c2.classid AND c2.name = 'English') ON t.teacherid = tc2.teacherid If you want to AND in a condition on NOT teaching a class, for instance Biology, put parentheses around the old joins and (substituting an appropriate counter for '3') add: LEFT JOIN (TeacherClass tc3 INNER JOIN Class c3 ON tc3.classid = c3.classid AND c3.name = 'Biology') ON t.teacherid = tc3.teacherid and add the following to the WHERE clause: AND tc3.classid IS NULL Give it a try and let me know if it works! Bruce Feist - 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
SQL Query Help
Can someone please help me with the following? Normally I would do this with a nested select, but since this is not available in MySQL I think I need help. Here is what I have: An order table with sales tax total and an orderdetail table with ordered, itemid and qty. What I need to do is form a sql query that will allow me to pull get the tax amount on all orders where product id is 1, 2 or 3 for example. The problem that I have is when I do a straight join on select tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I can get multiple tax amounts where an order has multiple matching records in orderdetail. I know that I can group by order.id, but what I eventually need to do is pull sum(tax) and not just tax. Is this making sense? David McInnis - 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: SQL Query Help
On Fri, Oct 04, 2002 at 12:36:30PM -0700, David McInnis wrote: Can someone please help me with the following? Normally I would do this with a nested select, but since this is not available in MySQL I think I need help. Here is what I have: An order table with sales tax total and an orderdetail table with ordered, itemid and qty. What I need to do is form a sql query that will allow me to pull get the tax amount on all orders where product id is 1, 2 or 3 for example. The problem that I have is when I do a straight join on select tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I can get multiple tax amounts where an order has multiple matching records in orderdetail. I know that I can group by order.id, but what I eventually need to do is pull sum(tax) and not just tax. I'm not certain if I understand what you're after... If you want total tax per order, try select sum(tax) as tax, orders.id from orders, orderdetail where orders.id = orderdetail.orderid and productid in (1,2,3) group by orderid; Otherwise, please clarify what you want in your desired result set. - 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: SQL query help
Hi Craig, you wrote--- select B.name,C.name from lookuptable A, user B, cat C where A.user = B.id and A.category = C.id and A.category in (3,5); The problem I see is that records come back where a user is in category 3 or category 5. How do I limit records so that the selected user must have category entries for both 3 and 5? (not in the same entry of course) looks like your lookuptable A will contain the relation between B and C? assuming this is true, u could try the the following sql query: select B.name, C.name from lookuptable A, user B, cat C where A.user = B.id and A.category = C.id and A.category in (3,5) group by A.user having count(A.user) 1 (this is because the user will have more than one listing in the lookup table). let me know if this works. sometimes, mysql's lack of subqueries is a little painful :) cheers/erick __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.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
SQL query help
I know this is an off topic question, but wondered if anyone might have a
moment to help. I wouldn't ask if I could do a sub-select...
I am using mysql v3.23.51 and have a table I need to display the information
of users who are working in both category 3 and category 5.
Table def looks kind of like:
lookuptable {
id int,
user int,
category int,
status tinyint,
...
..
}
A query I am using is kind of like the following:
select B.name,C.name
from lookuptable A, user B, cat C
where A.user = B.id
and A.category = C.id
and A.category in (3,5);
The problem I see is that records come back where a user is in category 3 or
category 5. How do I limit records so that the selected user must have
category entries for both 3 and 5? (not in the same entry of course)
Thanks for your time,
-Craig
-
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
SQL query help
I've been trying to solve this problem for a couple of days now. I've only started with DB's a month ago so I am no expert in SQL. So, I present this problem to you guys in hopes of getting a solution. Thanks in advance, Mike = Question: = Using the query below I get the result set as shown below. Everything is correct except the Last Src and Last Dst fields. I'm getting the same values in Last Src and Last Dst that I have in First Src and First Dst. What I want shown is the First Time an event occurs along with the source and destination ips for that first time and the Last Time an event occurs along with the source and destination ips for that last time. The event and iphdr tables are related by the primary keys sid and cid which are present in both tables. The event and signature tables are related by event.signature and signature.sig_id. The timestamps for First and Last time are located in the event table whereas the ips that I want to correspond to the First and Last time are in the iphdr table. The query is grouped by signature.sig_name. I've tried using min and max (inet_ntoa(iphdr.ip_src)) etc. However, this gives me the largest ip address numerically (not a big surprise [:)] ). This is not what I want. == Query: == select count(*) as Count, min(event.timestamp) as First Time, inet_ntoa(iphdr.ip_src) as First Src, inet_ntoa(iphdr.ip_dst) as First Dst, max(event.timestamp) as Last Time, inet_ntoa(iphdr.ip_src) as Last Src, inet_ntoa(iphdr.ip_dst) as Last Dst from event, signature, iphdr where event.signature = signature.sig_id and event.sid = iphdr.sid and event.cid = iphdr.cid and signature.sig_name not like spp_portscan% group by signature.sig_name order by count desc; === Tables: === event - Field Type Null Key Default Extra sid int(10) unsignedPRI0 cid int(10) unsignedPRI0 signature int(10) unsignedMUL0 timestamp datetime MUL-00-00 00:00:00 signature - Field Type Null Key Default Extra sig_id int(10) unsignedPRINULLauto_increment sig_name varchar(255) MUL sig_class_id int(10) unsignedMUL0 sig_priority int(10) unsignedYESNULL sig_rev int(10) unsignedYESNULL sig_sid int(10) unsignedYESNULL iphdr - Field Type Null Key Default Extra sid int(10) unsignedPRI0 cid int(10) unsignedPRI0 ip_src int(10) unsignedMUL0 ip_dst int(10) unsignedMUL0 ip_ver tinyint(3) unsignedYESNULL ip_hlen tinyint(3) unsignedYESNULL ip_tos tinyint(3) unsignedYESNULL ip_len smallint(5) unsignedYESNULL ip_id smallint(5) unsignedYESNULL ip_flags tinyint(3) unsignedYESNULL ip_off smallint(5) unsignedYESNULL ip_ttl tinyint(3) unsignedYESNULL ip_proto tinyint(3) unsigned0 ip_csum smallint(5) unsignedYESNULL Result: Count First TimeFirst SrcFirst Dst 4621 2002-03-15 09:40:44192.168.1.21063.105.25.187 2798 2002-03-12 15:59:0524.60.17.13192.168.1.210 2275 2002-03-12 15:48:1312.125.139.54192.168.1.210 2103 2002-03-12 16:06:25195.30.18.4192.168.1.210 1507 2002-03-15 09:50:37199.230.29.74192.168.1.210 1118 2002-03-12 15:54:14208.185.54.14192.168.1.119 1109 2002-03-12 16:34:35192.168.1.21066.77.73.155 Last Time Last Src Last Dst 2002-03-20 09:20:23 192.168.1.21063.105.25.187 2002-03-20 10:50:38 24.60.17.13192.168.1.210 2002-03-20 11:06:09 12.125.139.54192.168.1.210 2002-03-20 11:16:09 195.30.18.4192.168.1.210 2002-03-20 10:31:08 199.230.29.74192.168.1.210 2002-03-20 10:46:29 208.185.54.14192.168.1.119 2002-03-20 11:02:12 192.168.1.21066.77.73.155 ... (snip) - 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
sql,query help with performing an update on a SET datatype
OK, I cannot find this sql query on the website or in any of my manuals.
I have a table that contains a set datatype eg
CREATE TABLE testjason (
ID int(11) NOT NULL auto_increment,
SomeSetColumn set('a','b','c') NOT NULL default '',
) TYPE=MyISAM;
I neet to perform an UPDATE on this table. The catch is I dont want to UNSET anything.
In other words, I need to update the set values to make sure that 'c' is flagged, but
leave 'a' and 'b' in thier current state.
Dummy data before update
'1','a'
'2','a,b'
'3','a,c'
What I want AFTER update
'1','a,c'
'2','a,b,c'
'3','a,c'
Thanks
-
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: SQL Query Help
My best code is this: SELECT SUM(invoice_amount), state, statement_date FROM invoice GROUP BY state, statement_date The results are have followed: +-+---++ | SUM(invoice_amount) | state | statement_date | +-+---++ |65389.35 | Manitoba | 2001-12-01 | | 194224.45 | New Brunswick | 2001-12-01 | | 271516.40 | Quebec| 2001-12-01 | | 361673.95 | Quebec| 2002-01-01 | +-+---++ The main problem is the layout and the order of the results set. I will have 9 state (provinces) and I would prefer the month to be the columns. The ideal results would look like this, Please note I have only included 5 state and there is 9 but you see what I'm looking for, also the statement_date is always the 1st of the month: +-+---++ | SUM(invoice_amount) | state | statement_date | +-+---++ |0.00 | Alberta | 2001-12-01 | |65389.35 | Manitoba | 2001-12-01 | | 194224.45 | New Brunswick | 2001-12-01 | |0.00 | Ontario | 2001-12-01 | | 271516.40 | Quebec| 2001-12-01 | |0.00 | Alberta | 2002-01-01 | |0.00 | Manitoba | 2002-01-01 | |0.00 | New Brunswick | 2002-01-01 | |0.00 | Ontario | 2002-01-01 | | 361673.95 | Quebec| 2002-01-01 | +-+---++ Stephane -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 02, 2002 3:47 PM To: Danis Stéphane (NHQ-AC); [EMAIL PROTECTED] Subject: Re: SQL Query Help Stéphane, I have INVOICE table here is the layout: +-+---+--+-+-+-- --+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- --+ | ID | int(11) | | PRI | NULL| auto_increment | | CLIENT_NAME | int(11) | YES | | NULL| | | STATE | varchar(255) | YES | | NULL| | | STATEMENT_DATE | date | YES | | NULL| | | INVOICE_AMOUNT | double(16,2) | YES | | NULL| | | LAST_MODIFIED_DATE | timestamp(14) | YES | | NULL| | | ACTIVE_FLAG | tinyint(1)| YES | | NULL| | +-+---+--+-+-+-- --+ I would like to produce a result set that would give me the following report: it would be a sum of the INVOICE_AMOUNT grouped by state(STATE) and month(STATEMENT_DATE). ++-+-+-+-+ | MONTH | STATE_1 | STATE_2 | STATE_3 | ... | ++-+-+-+-+ | JANUARY|1234 | 12345 | 124 | | | FEBRUARY |2536 | 65874 | 457 | | | MARCH |4578 | 87452 | 547 | | | ...| | | | | ++-+-+-+-+ Any idea, I tried a bunch of different syntax without any solutions. mysql, query This can be done in a single query... How many different states do you want to list in columns? Would it be easier to list the months as columns/switch rows and cols? It doesn't much matter but are we talking significant numbers of rows? What is your best code so far/the problem(s) that need fixing? =dn - 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: SQL Query Help
From: Danis Stéphane (NHQ-AC) [EMAIL PROTECTED] My best code is this: SELECT SUM(invoice_amount), state, statement_date FROM invoice GROUP BY state, statement_date The results are have followed: snip The main problem is the layout and the order of the results set. I will have 9 state (provinces) and I would prefer the month to be the columns. The ideal results would look like this, Please note I have only included 5 state and there is 9 but you see what I'm looking for, also the statement_date is always the 1st of the month: +-+---++ | SUM(invoice_amount) | state | statement_date | +-+---++ |0.00 | Alberta | 2001-12-01 | |65389.35 | Manitoba | 2001-12-01 | | 194224.45 | New Brunswick | 2001-12-01 | |0.00 | Ontario | 2001-12-01 | | 271516.40 | Quebec| 2001-12-01 | |0.00 | Alberta | 2002-01-01 | |0.00 | Manitoba | 2002-01-01 | |0.00 | New Brunswick | 2002-01-01 | |0.00 | Ontario | 2002-01-01 | | 361673.95 | Quebec| 2002-01-01 | +-+---++ As far as as I can tell, the following query should deliver what you want: SELECT SUM(invoice_amount), state, statement_date FROM invoice ORDER BY statement_date, state Let me know if this does the job! -- denonymous www.coldcircuit.net - 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: SQL Query Help
Stéphane, Answers: There are nine different states (and there are 12 months). * The main problem is the layout and the order of the results set. I will have 9 state (provinces) and I would prefer the month to be the columns. The ideal results would look like this, Please note I have only included 5 state and there is 9 but you see what I'm looking for, also the statement_date is always the 1st of the month: * It would therefore not make sense to switch rows and cols! [It doesn't much matter but are we talking significant numbers of rows?] - I assume we are, so ... I said This can be done in a single query - let's get on with it:- INVOICE table layout: +-+---+--+-+-+-- --+ | ID | int(11) | | PRI | NULL| auto_increment | | CLIENT_NAME | int(11) | YES | | NULL| | | STATE | varchar(255) | YES | | NULL| | | STATEMENT_DATE | date | YES | | NULL| | | INVOICE_AMOUNT | double(16,2) | YES | | NULL| | | LAST_MODIFIED_DATE | timestamp(14) | YES | | NULL| | | ACTIVE_FLAG | tinyint(1)| YES | | NULL| | +-+---+--+-+-+-- --+ I would like to produce a result set that would give me the following report: it would be a sum of the INVOICE_AMOUNT grouped by state(STATE) and month(STATEMENT_DATE). ++-+-+-+-+ | MONTH | STATE_1 | STATE_2 | STATE_3 | ... | ++-+-+-+-+ | JANUARY|1234 | 12345 | 124 | | | FEBRUARY |2536 | 65874 | 457 | | | MARCH |4578 | 87452 | 547 | | | ...| | | | | ++-+-+-+-+ SELECT SUM(invoice_amount), state, statement_date FROM invoice GROUP BY state, statement_date +-+---++ | SUM(invoice_amount) | state | statement_date | +-+---++ |65389.35 | Manitoba | 2001-12-01 | | 194224.45 | New Brunswick | 2001-12-01 | | 271516.40 | Quebec| 2001-12-01 | | 361673.95 | Quebec| 2002-01-01 | +-+---++ +-+---++ | SUM(invoice_amount) | state | statement_date | +-+---++ |0.00 | Alberta | 2001-12-01 | |65389.35 | Manitoba | 2001-12-01 | | 194224.45 | New Brunswick | 2001-12-01 | |0.00 | Ontario | 2001-12-01 | | 271516.40 | Quebec| 2001-12-01 | |0.00 | Alberta | 2002-01-01 | |0.00 | Manitoba | 2002-01-01 | |0.00 | New Brunswick | 2002-01-01 | |0.00 | Ontario | 2002-01-01 | | 361673.95 | Quebec| 2002-01-01 | +-+---++ Let's deal with the months first (ref man: 6.3.4 Date and Time Functions). Put the month name in the left-hand column by extracting it from the statement date column: SELECT MONTH( statement_date ), as Month, SUM(invoice_amount), state FROM invoice GROUP BY state, statement_date should produce something like: | Month| SUM(invoice_amount) | state | | December |0.00 | Alberta | | December |65389.35 | Manitoba | | December | 194224.45 | New Brunswick | | December |0.00 | Ontario | | December | 271516.40 | Quebec| | January|0.00 | Alberta | | January |0.00 | Manitoba | | January |0.00 | New Brunswick | | January |0.00 | Ontario | | January | 361673.95 | Quebec| and while we're dealing with temporal matters, don't forget that if you have more than twelve month's worth of data March/April may appear more than once in the output! The next step is to get those states into columns instead of rows. Let's start with Alberta (where else?) - and only Alberta - so what we'd like to see is something like: SELECT MONTH( statement_date ), as Month, SUM(invoice_amount) FROM invoice WHERE state = Alberta GROUP BY statement_date | Month| SUM(invoice_amount) | | December |0.00 | | January|0.00 | but that won't scale up because when we work with more than one state, the WHERE clause is going to end up mixing state/data/lists again. Is there another way to achieve the
RE: SQL Query Help
I didn't have time to try out your hints/techniques described in your earlier post, but after playing around a bit with the web frontend I decided that it would be more user intuitive to used the month as column and the state as row... The reason being my change of heart is I also have a similar report to build for cities, so I prefer using the variable data (ie. state/city) as rows and keep the fixed data (ie. month) as column. Stephane -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 11:01 AM To: Danis Stéphane (NHQ-AC); [EMAIL PROTECTED] Subject: Re: SQL Query Help Stéphane, Have just re-read my response... * The main problem is the layout and the order of the results set. I will have 9 state (provinces) and I would prefer the month to be the columns. The ideal results would look like this, Please note I have only included 5 state and there is 9 but you see what I'm looking for, also the statement_date is always the 1st of the month: * After asking you if the months and states/rows and columns could be transposed, was I dozy enough to suggest an answer that is the wrong way around??? Whilst I would recommend that the answer table be constructed to have fewer columns than rows, maybe you have your reasons... If you do want to transpose the answer given, can you manage it from the hints/techniques described, or do you need me to take another run at it? Regards, =dn - 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
SQL Query Help
I have INVOICE table here is the layout: +-+---+--+-+-+-- --+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- --+ | ID | int(11) | | PRI | NULL| auto_increment | | CLIENT_NAME | int(11) | YES | | NULL| | | STATE | varchar(255) | YES | | NULL| | | STATEMENT_DATE | date | YES | | NULL| | | INVOICE_AMOUNT | double(16,2) | YES | | NULL| | | LAST_MODIFIED_DATE | timestamp(14) | YES | | NULL| | | ACTIVE_FLAG | tinyint(1)| YES | | NULL| | +-+---+--+-+-+-- --+ I would like to produce a result set that would give me the following report: it would be a sum of the INVOICE_AMOUNT grouped by state(STATE) and month(STATEMENT_DATE). ++-+-+-+-+ | MONTH | STATE_1 | STATE_2 | STATE_3 | ... | ++-+-+-+-+ | JANUARY|1234 | 12345 | 124 | | | FEBRUARY |2536 | 65874 | 457 | | | MARCH |4578 | 87452 | 547 | | | ...| | | | | ++-+-+-+-+ Any idea, I tried a bunch of different syntax without any solutions. Stephane 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: SQL Query Help
Stéphane, I have INVOICE table here is the layout: +-+---+--+-+-+-- --+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- --+ | ID | int(11) | | PRI | NULL| auto_increment | | CLIENT_NAME | int(11) | YES | | NULL| | | STATE | varchar(255) | YES | | NULL| | | STATEMENT_DATE | date | YES | | NULL| | | INVOICE_AMOUNT | double(16,2) | YES | | NULL| | | LAST_MODIFIED_DATE | timestamp(14) | YES | | NULL| | | ACTIVE_FLAG | tinyint(1)| YES | | NULL| | +-+---+--+-+-+-- --+ I would like to produce a result set that would give me the following report: it would be a sum of the INVOICE_AMOUNT grouped by state(STATE) and month(STATEMENT_DATE). ++-+-+-+-+ | MONTH | STATE_1 | STATE_2 | STATE_3 | ... | ++-+-+-+-+ | JANUARY|1234 | 12345 | 124 | | | FEBRUARY |2536 | 65874 | 457 | | | MARCH |4578 | 87452 | 547 | | | ...| | | | | ++-+-+-+-+ Any idea, I tried a bunch of different syntax without any solutions. mysql, query This can be done in a single query... How many different states do you want to list in columns? Would it be easier to list the months as columns/switch rows and cols? It doesn't much matter but are we talking significant numbers of rows? What is your best code so far/the problem(s) that need fixing? =dn - 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: SQL QUERY help joining three tables
* John Hughes I have three tables: students has student_id and student_name parents has parent_id and parent_name parentlog has student_id and parent_id I want to search the parentlog WHERE student_id = some_id GROUP BY parent_id (This will bring back two rows when there are two parents) At the same time I want to get the name of the student that matches student_id and the name of the parent. I can LEFT JOIN students with parentlog USING(student_id) but I can't figure how I can join the parents so that I can get the name of match for the parent_id. Can I join three tables and search all in one pass? Yes, and LEFT JOIN may not be needed: SELECT students.*,parents.* FROM parentlog,students,parents WHERE students.student_id = parentlog.student_id AND parents.parent_id = parentlog.parent_id Using LEFT JOIN: SELECT students.*,parents.* FROM parentlog LEFT JOIN students USING(student_id) LEFT JOIN parents ON parents.parent_id = parentlog.parent_id USING can not be used in the second join because it relates to the previous table, which in this case is students, and that table has no parent_id. By using ON you can join with any table in your table list. More details can be found in the manual: URL: http://www.mysql.com/doc/J/O/JOIN.html -- Roger 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
SQL QUERY help joining three tables
SQL QUERY question I have three tables: students has student_id and student_name parents has parent_id and parent_name parentlog has student_id and parent_id I want to search the parentlog WHERE student_id = some_id GROUP BY parent_id (This will bring back two rows when there are two parents) At the same time I want to get the name of the student that matches student_id and the name of the parent. I can LEFT JOIN students with parentlog USING(student_id) but I can't figure how I can join the parents so that I can get the name of match for the parent_id. Can I join three tables and search all in one pass? TIA John Hughes __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.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
