Report Writing Query Problem (joins Again...)
Hola database type folks,
I have been trying to get this right for sometime but now must admit defeat
I have 3 tables
artists -> holds details on the artists
card_gallery -> hold details of card available related to artists via artist_name
scart -> shopping basket related to card_gallery via card_image_id
I am writing a reporting facility that allows the administrator to view all the artists in a table along with their number of cards online and cards sold figures.
Currently I have something like
select
card_gallery.artist_name as cg_an,
count(card_gallery.artist_name) as cg_ca,
count(scart.card_image_id) as s_ci,
from
card_gallery
left join
scart
on
card_gallery.card_gallery_id=scart.card_image_id
group by card_gallery.artist_name
but that doesnt give the correct number of cards sold.
Cards sold are registered in the scart table by setting the processed field to 1
Ideally I would like to be able to view all the artists on one table with the number of cards online and the number sold even if it's 0
View Complete Forum Thread with Replies
Related Forum Messages:
Writing A Query
I have 3 tables, A, B and C. A aggregates B, and B aggregates C. the point is that A creates an instance of C, which is appended to B. C stores the primary key of the A that created it in a field ("c_ID"), although there's no direct relationship between the two (c_ID is NOT a foreign key). however, it might be possible to reassign an instance of B to another A - in this case, the C instances which A created for this B are no longer of A's concern. there are 3 databases, and i'm trying to select the instances of C that were created by A AND are from instances B presently aggregated by A. could someone give me a hand on the SQL to write this? i think it'd look something like: SELECT * FROM C WHERE C.b_ID = (every B that A aggregates) AND C.c_ID = (this A's a_ID)
View Replies !
Need Help Writing Query
I'm trying to write a query against a table that has four fields: make model year timestamp I want the results to return a list of distinct model, make, and year, along with a count of the number of records that fall into each model/make/year combination, sorted in descending count. I'd also like to be able to specify the time range less than 90 days and ignore any records that fall outside that. This is beyond my entry level knowledge of MySQL,
View Replies !
Is There Any Better Way Of Writing This Query?
Query: Quote: SELECT ided FROM affil WHERE (LEFT(name, 1) = Ɔ' OR LEFT(name, 1) = Ƈ' OR LEFT(name, 1) = ƈ' OR LEFT(name, 1) = Ɖ' OR LEFT(name, 1) = Ɗ' OR LEFT(name, 1) = Ƌ' OR LEFT(name, 1) = ƌ' OR LEFT(name, 1) = ƍ' OR LEFT(name, 1) = Ǝ' OR LEFT(name, 1) = Ə') AND app=10 Is there any better way of doing this query. I tried using the IN(1,2,3,4,5,6,....) but couldn't get that to work so I don't know if I were doing it correctly or not.
View Replies !
Advanced Query Writing
I have a table populated with people's information (first name, last name, birth date, etc). I'm trying to write a query that will pull everyone with the same last name. So far, I have this code which will successfully tell me which last names occur more than once: SELECT last_name, count(*) FROM accts GROUP BY last_name HAVING count(*) > 1; I'd like to build in a sub query to return the first and last names where the last name matches the query above. Not sure how to approach this, or if it's even possible.
View Replies !
Help Needed Writing Query
I'm trying to make a query to return 10 sorted values, but I need to calculate the value to sort by, ie its not stored as a field in the table. So say I had some data like this... id basicrank time 1 5 40 2 7 33 3 3 55 etc. I want to sort by (basicrank minus (currenttime - time)), so newer records get some priority, returning highest score first. My query could then be something like this, given current time of 60. SELECT id FROM table ORDER BY (basicrank-(60-time)) DESC LIMIT 10
View Replies !
Writing A Multi-table Query
I have a database with a table of concerts, a table of artists, and a relational table, containing pairs of event ids and artist ids (all my tables have an 'id' column that auto increments). Here's what I am trying to find: I want to find the ids of all events where a certain artist is playing, that are happening to today or later, ordered by date. I'm doing this in PHP, do I need to write multiple queries, or is there a way to do this querying once?
View Replies !
Report Query Load
Is there a way in PHP to have the MYSQL report the load that is placing on the mysql server. I am trying to optimize some queries and I was hoping the server could give me some feedback so I know if I'm going in the right direction.
View Replies !
Report Of Query Execution Times
Does mysql have a tool to record and report a list of all queries executed and how long it took to execute each query? If not can anyone suggest a tool I can use to do this? I've heard of mysql query profiler but does it capture queries from all sessions?
View Replies !
Joins Query
I have a page whereby I list all events and flyers for each event. Because each event can have more than one flyer it's obviously a many-to-many relationship, so I have introduced a new table called event_flyers which has the following fields; event_id, flyer_id. How *should* the query look? SELECT * FROM calendar, flyers LEFT JOIN calendar_flyers ON calendar_flyers.flyer_id = flyers.id WHERE calendar_flyers.calendar_id = calendar_id
View Replies !
Relational Query And JOINs
I'm trying to set up a simple invoicing system, and am having trouble figuring out the right query to retrieve data from three tables... The important fields in the tables are: Customers: CustomerID, Name, Address etc etc Orders: OrderID, CustomerID, PaidSoFar OrderRows: OrderID, ProductID, RowTotal I need to make a list looking something like this:
View Replies !
To Re-write Complex Query With JOINS
I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 12004-01-10110 22004-01-20120 32004-01-20270 42004-01-10280 52004-01-15110 62004-01-25310 Output : RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 22004-01-20120 32004-01-20270 62004-01-25310 Now I want to use a single SQL query to find a result where there exist one record for each unique "othertableid" where the record selected for the "othertableid" should be the recent one with regard to "EffectiveDate". That is from the above records, I want to select Records with "RecordId" = 2 and 3 because they are the recent one for "othertableid" = 1 and 2 respectively. Please be sure that I want to retrive all fields including "RecordId". The result should not depend on any other fields but "EffectiveDate" only. I am using MySQL 4.0.12 and it does not support "SUBQUERIES" which is now given support in latest MySQL edition. But I have read in the manual of MySQL that any "SUBQUERY" SQL statement can be written with proper "JOINS".
View Replies !
Design / Query Confusion - Joins?
Sorry for the long post here. I'm trying to give enough info. to explain my situation, and am novice enough to not know how much someone might need to know to help. I'm very new to SQL, I've read through the Sitepoint "Build Your Own Database Driven Website" book, and though I did follow it, I didn't build the joke database, I just converted it to the specific task I want to do to organize some stuff for my department at work. Basically what I am doing is tracking design drawings; Job name and number, clients, architects, when drawings leave and return for approvals, to the shop and field, etc... Getting all the information that our detailers might need, in one place. Where I'm running into problems, is designing the database with some thought on how we set up our job numbers and attempting to keep the tables fairly small by creating tables for each year. A typical job number would be something like: B06-064 (B is first initial of the Project Manager - 06 for the year - and 064 for the 64th job that year) 04_jobs, 05_jobs, and 06_jobs all have the same columns: CREATE TABLE 04_jobs ( job_year YEAR(2) NOT NULL DEFAULT ཀ', id SMALLINT(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, job_name VARCHAR(255), pm_id TINYINT(3), client_id SMALLINT(4), arch_id SMALLINT(4), finished SET(Ɔ', Ƈ') NOT NULL DEFAULT Ɔ', PRIMARY KEY ( job_year, id) ); The job_year seems a bit redundant since the tables are named by year- I was thinking the problem with my select query had to do with the duplicate PKs (id) across tables - this may be a good indication of how little I understand everything - I just added this column and changed the PK this morning. When I run a query such as: SELECT * FROM 04_jobs, 05_jobs; I get rows joined together - each job in each tabled joined on rows. not what I was expecting. - getting a row for each job - as if the query were on a single table. When I run a query such as: SELECT * FROM 04_jobs, 05_jobs WHERE pm_id="1"; I get an error that the clause is ambiguous. I guess where I'm most confused is not knowing if its: 1. a design flaw 2. a query flaw (everything I've read so far on JOIN seems directed at joining rows - which isn't what I want) 3. Something that is easily handled with a PHP loop (I'm new to PHP too). I'm doing everything on a command line for now, I haven't written any php for this area of the project.
View Replies !
How To Convert SELECT Query (with Left Joins) Into UPDATE ?
SELECT t0.type, t0.datekey, t1.datekey as prev_seen FROM t_detail t0 LEFT JOIN t_detail t1 ON t0.type = t1.type AND t0.count-1 = t1.count order by t0.type, t0.datekey ASC It creates a table with the 'most recent previously seen' date for each date, count is already used to number the 'types'. I used it to create a new_table - OK and added an auto increment id - OK. Now I want to modify the select query to update new_table (which is a complete refresh of its contents, that's ok) how to do with the left join?
View Replies !
Writing Triggers
I need to write trigger which will update 3-4 table ,with relevant records.The sceneriao is as under.We have 2 databases server.We have set up the replication successfully with these 2 servers for database A .One is the master (MYSQL 4.1) on linux and the second one is slave(MYSQL 5.0) on windows. Now on the slave server we have a another db B which consist of 5 tables.We need to fire a trigger to insert,update,delete records into these 5 tables every time data is replicated to slave.The problem is that not the entire record is to be inserted from the replicated table.Only few coloumns is to be iserted,say col1,col5 , col9 to be inserted in table1;col1,col3,col8 to be insrted in table2 ;col 3,col6,col7 in table3 and so on.I am wondering how to achive this.Please help me in this .Its really very urgent.
View Replies !
Writing A Procedure
I've searched the forums, but I haven't found my answer. I'm new to stored procedures, but I'm told that they can decrease development time, etc by using them. I have MySQL 4.1, I downloaded the Windows (x86) version. I looked at MySQL's online manual, but it referrs me to look for some files on my hard drive that will explain how to write a procedure.sql/sql_analyse.ccsql/procedure.hsql/procedure.ccsql/sql_select.ccThe problem is I can't find these files on my drive.
View Replies !
Writing Records
This is related to my last post. I have a table with a 10-field unique index. I have a scheduled script which writes records to the table (in the form of a number of SELECT... INSERT statements).I had an issue which meant that apparently duplicate records were being written to the table, despite the unique index, but this turned out to be because some of the values are NULL, and in MySQL no two NULL's are ever identical (thanks to Felix Geerinckx for pointing that one out). So, my solution to this is to write a Zero instead of a NULL. This creates a new problem - the script stops executing as soon as it attempts to write the first record which violates the unique index. Even if the SELECT statement identifies 20 records which need to be written, if the first one is a duplicate, it fails to write all 20 records, not just the one unique record.
View Replies !
Not Writing To Database
I have a MySql database on a Redhat/Apache server at work. The database (which I'm told once worked) is now not capturing the data from a PHP email form. I tested the code on another MySQL server and it works there. But when I try it again (after changing server/user/pswd back to correct info) on the original server at work, it won't write the data to the database. Doesn't give any errors, just doesn't write.
View Replies !
Writing Dates
I want to write the current datetime value into a MySQL 4.1.18 database using Java. I am using a Struts Action class(servlet) for this. I have kept the Date columntype as DateTime.I am using the java.sql.Date class. Following is the date code: DateFormat df=DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.FULL); String datetime=df.format(new Date()); When writing to the MySQL column, the value being saved is 0/0/0000.
View Replies !
Writing To MySQL
Writing a C program (using tookit 2003 to compile) that can read and write to a MySQL database. I have a couple of questions about writing to the database. 1) Right now I can connect to database server on a different computer (networked). I input the host, username and password and it connects fine. Do I also have to have it connect to a certain database and table for this to write correctly? 2) If I do have to connect to a database and table, how do I get around this error: "error C2660: 'SQLConnect' : function does not take 11 arguments" I've already changed the header file to include the extra space for the database and table names... 3) Where could I find a good tutorial about writing from a C program to a database in Windows XP Pro?
View Replies !
Writing A Statement
table.jpg I've attached a picture of my table so someone may be able to help me write the SQL statement. I need 2 queries: 1st query should be to find and list all the 'buck' from column g, g2, g3, g4. 2nd query should be to find and list all the 'doe' from column g, g2, g3, g4. In my statement I want to: SELECT sID, today, sLastname, sFirstname, g, g2, g3, g4 FROM member ?????????? ORDER BY today DESC
View Replies !
Writing Information To A Database.
I have a server setup to run MySQL and php and I was wondering how to write information from a site to the db. So far ive tried with .asp and dreamweaver, and to no avail. And well, seem to find PHP abit easier to code with. At least for me haha. Could anyone show an example piece of code that shows how the write command is written to direct it to the database? or a reference as to where I can read up more on it. Ive searched for abit and have not found anything fruitful. Ive found w3schools, which has helped me to understand it alil bit more, but not enough to be able to write to the database.
View Replies !
Writing To Two Tables (+auto Increment)
I have two tables, both of which have userID as a primary key. I INSERT an entry into the first, using NULL for userID, as it is set to auto-increment. Then I want to immediately retrieve that newly generated userID and write it to the second table, with some other data. But when I do an INSERT on the first table, then straight away try to read the userID, I always get 0 returned.
View Replies !
Writing Multi-threaded Mysql Application
What I'm trying to do is to rewrite mysql client application (which calculates ISP dial-up customers' billing)into multi-threaded version. I'm looking for some resources on multi-threaded mysql application programming and it will be great if somebody knows some tutorials, samples and howto documentations on the web. I searched the web and didn't find anything related to mysql multi-threaded programming.
View Replies !
Huge Performance Difference In Two Forms Of Writing A Subquery
I wrote the following subquery to return the names of people that have a phone number in common with someone else: SELECT Name FROM People WHERE Phone IN (SELECT Phone FROM People GROUP BY Phone HAVING COUNT(*) > 1) The table has about 100K rows and this takes about 20 seconds which does not make sense because running the subquery by itself takes only about 0.20 secs and the main query about that much if I enter the result of the subquesry manually (10 rows returned). So I would have expected this to run in 0.40 seconds and not 20! So MySQL must be doing something wrong here, interpreting this the wrong way or something or somehow running the subquery multiple times? Though I can't see why. Playing around with this a little I then (almost accidentally) found that if I rewrite the above as: SELECT Name FROM People WHERE Phone IN (SELECT Phone FROM (SELECT Phone FROM People GROUP BY Phone HAVING COUNT(*) > 1) InnerTable) Does indeed run in only 0.40 secs!!! Now I can't figure out why these two forms should behave so differently. I can't understand why MySQL would interpret the first one in any other way than the obvious and even more puzzling (to me) that the second form fixes the problem. I am happy to use the second form but I would also like to understand why this difference. The only other problem is that the second (and faster) form is not allowed to be written as a View, it complains that the View's SELECT contains a subquery in the FROM clause. Here are the explain plans for the two queries (I left out the columns that were NULL for all rows): First Form (SLOW): +----+--------------------+--------+------+--------+----------------------------------------------+ | id | select_type | table | type | rows | Extra | +----+--------------------+------- +------+--------+----------------------------------------------+ | 1 | PRIMARY | People | ALL | 108423 | Using where | | 2 | DEPENDENT SUBQUERY | People | ALL | 108423 | Using where; Using temporary; Using filesort | +----+--------------------+--------+------+--------+----------------------------------------------+ Second Form (FAST): +----+--------------------+------------+------+--------+----------------------------------------------+ | id | select_type | table | type | rows | Extra | +----+--------------------+------------+------+--------+----------------------------------------------+ | 1 | PRIMARY | People | ALL | 108431 | Using where | | 2 | DEPENDENT SUBQUERY | <derived3> | ALL | 3 | Using where | | 3 | DERIVED | People | ALL | 108431 | Using where; Using temporary; Using filesort | +----+--------------------+------------+------+--------+----------------------------------------------+ Note that there is also a time difference to come up with each plan, the first one (SLOW) takes no time (0.00 sec) while the second one takes 0.20 sec, not sure if this matters or is in any way significant.
View Replies !
Report Creator
I am now using Open Office to connect to the MySQL thru ODBC. So that I can retrieve some data from MySQL and generate report in open office. I have already installed MyODBC and unixODBC. A file called odbc.ini created as below: [MySQL-test] Description = MySQL database test Driver = MySQL Server = localhost Database = test Port = 3306 When I tried to test this ODBC connection using isql MySQL-test test, an error occured: [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified [ISQL]ERROR: Could not SQLConnect
View Replies !
Report Tool
Is there any MySQL reporting tool that is open source and doesn't require programming knowledge? I need a front end for MySQL that will allow a user to input a couple parameters and will produce a report, including graphs. I haven't been able to find an adequate open source solution for this.
View Replies !
Mysqladmin 1.4 Report
when i try to create a report in mysqladmin 1.4 it a message box displays "the server must be connected" Under the enviroment tab the myODBC box has "not found Driver 3.51 not found I am using xampp can you tell me what i'm doing wrong?
View Replies !
Report Writer
couldn't really find a spot to put this, but basically I need a MySQL report writer. I have been using SQuirrel, but it isn't properly representing BLOB and DOUBLE values, looks like the values come out the other end as hexadecimal. Rather than spend time sortign it out I'm just gong to move to another report writer. It is unfortunate as I have really enjoyed using SQuirrel.
View Replies !
Report Generation
I have the MySQL server and the Query Browser up and working but I don't see anything about generating reports and I can't find anything on reports in the online documentation. What do MySQL users use for generating customized reports? Is there some GUI tool from MySQL or do people use other products like Crystal Reports?
View Replies !
Weekly Report
I am creating weekly reports for a application that I have. I'm using the Date column ( YYYY-MM-DD) for my mysql database. Is there a query that brings up totals for Monday through Sunday for the month of April/June etc??
View Replies !
Select For A Report
I have a table with this fields : ID (integer) Date (datetime) Name (char) I want select count of items that add in each month of year For example a report like this : 2 Items add to database in january of 2006 9 Items add to database in march of 2006 3 Items add to database in january of 2007 (I can change the numbric month to it names with PHP)
View Replies !
Writing Large Chunks Of Binary Data To MySQL With ODBC
I'm having a lot of trouble writing large chunks of binary data (tests are in the range of 16-512K, but we need support for large longblobs) to MySQL using ODBC. Database is local on a W2K system, but I have to support all modern Windows systems, and a variety of ODBC configurations. (I'll be testing against multiple ODBC databases soon - but development is against MySQL) I've been able to adapt some example code that executes a statement (SQLExecute) and uses SQLBindParameter to mark the longblob field as SQL_DATA_AT_EXEC. That works. However I really need to use a result set (opened for update), and to add new rows using SQLBulkOperation( hStmt, SQL_ADD) This works for the normally bound fields, but fails if I bind a variable using something like: Code:
View Replies !
Natural Joins And Joins With USING
I was wondering if there is a way to make MySQL 5.0.15 ( final release ) able to use natural joins and joins, using old code that worked with 5.0.11 and earlier. Since I am new to starting mysql and modifying it, more information the better.
View Replies !
Client Side Report
i hav a few reports that generate at server side i would like to copy the tables needed for the report to client's temp file, and perform the calculation and filtering there. may i know is this way possible? for 1 table, there's .frm, .myd and .myi files, which files should i copy? and how should the connection string looks like when connecting to client's own temp file?
View Replies !
Generate Report Using Group By
I have few invoices which have due_date as one of its field. Now I want to generate a report like the following CName --------0-30 -------- 31 - 60 --------61 - 90 -------- over 90 abc --------$45 -------- $34 -------- NO -------- No Xyz --------No -------- $78 -------- $76 -------- No bcd -------- $897 -------- $456 -------- No --------No Totals: ---- --- --- --- Where those days are overdue period (today - due date). I want to retrieve the whole in one (max 2 not a buch in a loop) queries.
View Replies !
Parameters In SP When Calling A Report
Set up the connection to the DB in Crystal using MyODBC. I used the Database expert to call my Stored Procedure which has one Parameter and the code is real simple going like this: call SuccessLetter (AppNo) From the wizard i can see and place all the fields from the SP onto the report as long as i hard code an application number into my parameter. But as soon as i change it to a variable (as above) i get the following error from Crystal: Unknown Fields AppNo in order clause MySQL ODBC 3.51 error 42S21 On the VB code side i used Success.SetParameterValue("AppNo", GeneralFunctions.ApplicationNo) to set up the parameter and this does get passed thru so the error is comming from the database expert - almost as if it dont recognise that my stored procedure has a parameter. Which it does and it does work if i use the SP outside of Crystal. I know the SP is working fine cause i have written another SP and then called SuccessLetter with the new SP using a parameter and the results are perfecr.
View Replies !
INSERT IGNORE - Get A Report?
I'm creating a shopping cart-esq type system with a primary key on two fields. My app builds an insert query that can insert many rows at once. When the user tries to add something already in their cart (not allowed by the primary key structure) the IGNORE statement allows the query to go ahead, and the other rows to be inserted. My question therefore is: Can I get a response from MySQL as to whether or not the IGNORE statement affected the query ? i.e would there have been a duplicate row ? Ideally I could even get info about WHICH row would have caused a conflict, but I'd be surprised if I could get this info.
View Replies !
Structure Of Database To Prepare Report
I'm attempting to create a database which will take information from one (perhaps two) tables and utilize that information to return queries to a report designed in Excel. The general idea I have is this: The "primary" or "source" table is easy. This is a trial balance (account number as a primary key, description, amount). I'll need table(s) to denote which account numbers belong to which page/line on the report. I'm not sure how many pages the report will be, but let's say my Excel workbook would have 50 sheets. My question is this: What would you recommend for a table structure. The way I see it, I could have a single table listing each page and each line on each page (each identified by a unique id), then have another table listing each account and each page/line id. The other option I see is to have a separate table for each "page," but I'm not sure I see a benefit in this. My first instinct is to use the first method I mentioned.
View Replies !
Crystal Report, MySql, And Images
I am creating a report using Crystal Report and I want to upload an image coming from the mysql database to the crystal report. Can I upload the image to crystal report without actually storing the image in the database?
View Replies !
Multiple Count Queries -> One Report
I'm getting a bunch of data out of a database in the form of a lot of COUNT statements eg: SELECT COUNT (*) AS "Value1" FROM tablename WHERE x=1; SELECT COUNT (*) AS "Value2" FROM tablename WHERE y=5; etc ... there are about 30 queries. I'm using PHPmyAdmin to do this. Currently I have to put each statement in separately: If I put the whole load of them, PHPadmin only displays the last value. I don't need a great deal of detail, but if someone can suggest a way I can accomplish this so I run one multi-line query and get all my values at once that would be great. Perhaps there is a better tool than phpMyadmin? Or maybe I should be looking at VIEWs or STORED PROCEDURES? If you can paint the broad outlines, I can fill in the gaps.
View Replies !
How To Show Records In A Report View
my table contains the following columns : news_date (date) news (tinytext) i need a query which would display the table records in the following way: 09-11-06 the news item------------- --------------------------- --------------------------- 10-11-06 the news item------------- ------------------------- ------------------------- thats is each record should be displayed as seperate and not in a tabular view.
View Replies !
|
TRACKER
