#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0

    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.
    skydrive.live.com/redir?resid=3F0B2DD98F3A474!286
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    1. we don't solve homework assignments on this site (although we will help you debug your efforts)

    2. the data is totally inconsistent
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    3. We dislike the word 'urgent'.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0

    re


    Its not a homework assignment. I just need general sql statement for this type of work/table/output.

    Thx
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0
    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!!!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    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:

    Code:
    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 
    ,PRIMARY KEY(date,pid)
    );
    
    INSERT INTO table1 VALUES
    (1,'Packet-Eye','P001'),
    (2,'Wiggy','W099'),
    (3,'Wimax-Lite','W001'),
    (4,'Wimax-Home','W002');
    
    INSERT INTO table2 VALUES
    ('2009-01-01',1),
    ('2009-01-12',1),
    ('2009-02-14',2),
    ('2009-03-03',3);
    
    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        |
    +---------------------+-------------------+
    Personally, I prefer to handle date formatting at the application level. It's often simpler to let the user or the user's locale 'choose' the desired format with a bit of javascript.

    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).
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0
    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.

    Thanks again!!
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Code:
    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 
    GROUP 
        BY t1.another_thing
         , dt;
    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.

    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 08:46 AM.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Sazrazz771
    Its not a homework assignment. I just need general sql statement for this type of work/table/output.
    i call bs

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0
    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.
  20. #11
  21. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    Originally Posted by Sazrazz771
    Alrite guys I apologize for all the "don'ts" of this forum - my first post gimme a break!
    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:

    Forum rules

    And people are going to be suspicious of "homework" type questions from new forum users because it happens a lot.

IMN logo majestic logo threadwatch logo seochat tools logo