September 19th, 2012, 05:11 AM
SQL query-table help urgent
Hi guys -I am new in here - Last time I worked on some sql queries was back in 2007. Now I need some help with this problem. Please let me know the solution of this problem.
September 19th, 2012, 06:02 AM
1. we don't solve homework assignments on this site (although we will help you debug your efforts)
2. the data is totally inconsistent
September 19th, 2012, 06:05 AM
3. We dislike the word 'urgent'.
September 19th, 2012, 06:15 AM
Its not a homework assignment. I just need general sql statement for this type of work/table/output.
September 19th, 2012, 06:20 AM
Alrite guys I apologize for all the "don'ts" of this forum - my first post gimme a break! I just need help with the problem at hand if anybody can guide/help that will be much appreciated. thanks!!!
September 19th, 2012, 06:55 AM
Welcome to the rough and tumble of devshed!
r937's point (which I thought he expressed rather eloquently) was that the data shown in the 'outputs' does not correspond to that provided in (the sample) data set.
This makes it hard to guess what you want.
Consider the following:
DROP TABLE IF EXISTS table1;
CREATE TABLE table1
(pid INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,productdesc VARCHAR(30) NOT NULL UNIQUE
,productcode VARCHAR(20) NOT NULL UNIQUE
DROP TABLE IF EXISTS table2;
CREATE TABLE table2
(date DATE NOT NULL
,pid INT NOT NULL
INSERT INTO table1 VALUES
INSERT INTO table2 VALUES
SELECT t1.productdesc `Product Description`,t2.date `Registration Date` FROM table1 t1 JOIN table2 t2 ON t2.pid = t1.pid;
| Product Description | Registration Date |
| Packet-Eye | 2009-01-01 |
| Packet-Eye | 2009-01-12 |
| Wiggy | 2009-02-14 |
| Wimax-Lite | 2009-03-03 |
Output2 appears to be the product of a PIVOT TABLE. Although this kind of thing can be done in MySQL, again it's often best to return an ORDERed result and handle the display logic at the application level (e.g. with a bit of PHP).
September 19th, 2012, 07:20 AM
Thanks a ton! I owe you a latte! Your just made me have a flash back from the past when I used to play around with these sql statements...
For the second output I am guessing a COUNT(Product_Description) has to be used on output1 and then probably a Group by Month ... if someone can just type the statement out that will be great.
September 19th, 2012, 07:41 AM
Strictly speaking, you should only GROUP BY unaggregated fields identified in the SELECT clause, but I'm OK here because I've previously established that certain fields are UNIQUE.
SELECT t1.something Product
, DATE_FORMAT(t2.something_else,'%Y-%m') dt
, COUNT(*) total
FROM table2 t2
JOIN table1 t1
ON t1.another_thing = t2.another_thing
Also I've not included an ORDER BY clause because the ORDER is implicit within the GROUP BY clause.
Finally, note that 'product-months' for which there are no results will not appear in the output. For that you must OUTER JOIN to the products table and either include a calendar utility table (containing all possible months or dates) or once again (and my preference), handle the logic for missing results at the application level.
Last edited by cafelatte; September 19th, 2012 at 07:46 AM.
September 19th, 2012, 07:54 AM
i call bs
Originally Posted by Sazrazz771
first of all, it's written as an assignment ("using table1 and table2, construct 2 sql scripts...")
but more tellingly, why would you put "urgent" unless it was something you had to hand in soon
if you're just interested in learning, why not wait until next week
September 19th, 2012, 08:11 AM
Thx for all your help with this.
@R937 its not an assignment its a side task Im taking at work- looking at some historical data. I was pretty good at data mining pre-2007 but after that my managerial position didnt involve sql tasks. Hence I needed some help.
September 19th, 2012, 09:07 AM
As a new user to a site it is always advisable to read FAQs and forum guidelines. The faq link is at the top of the site and here is a link to specific forum rules:
Originally Posted by Sazrazz771
And people are going to be suspicious of "homework" type questions from new forum users because it happens a lot.