Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2009
    Posts
    11

    Unanswered: Joining two tables and the LIKE function

    Hi, I am having some difficulty with sql perhaps someone could help me.
    What I'm trying to do is is make an sql report that joins a customer table and a product table to produce one table that display customer purchases. These purchases must be only from March and the customer must have a postcode (zip code for the Americans). I have managed to join them successfuly I am just stuggling with the LIKE function as these conditions are parts in a whole field... i.e. MAR is only the month of the entire date field.

    Code:
    SELECT c.CUSTNO as "Customer ID", c.CUSTNAME as "Customer Name", c.CUSTPOSTCODE as "Postcode", p.pID as "Product ID", p.purchaseDATE as "Purchase Date"
    FROM customers c JOIN products p
    WHERE c.CUSTPOSTCODE LIKE 'M33 %'
    AND p.purchaseDATE LIKE '%MAR%'
    Could someome please show me where I'm going wrong please?

    Thank you

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    DATE columns cannot be compared using LIKE, that is only for character data.

    To limit the SELECT on products that were purchased in march use:

    WHERE extract(month from p.purchaseDATE) = 3

  3. #3
    Join Date
    Jan 2009
    Posts
    11
    How does it know that 3 is the same as 'MAR' in the date field? also it doesn't seem to like:
    FROM customers c JOIN products p
    is that incorrect?

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Quote Originally Posted by Smush
    How does it know that 3 is the same as 'MAR' in the date field? also it doesn't seem to like:
    FROM customers c JOIN products p
    is that incorrect?
    What do you mean, "it doesn't seem to like .."? Are you getting an error message or are the results not what you expect?

    Please list DDL for tables,
    sample data,
    and expected results.

  5. #5
    Join Date
    Jan 2009
    Posts
    11
    Yes I get a pop-up saying:
    Error encountered
    ORA-00905: missing keyword
    00905. 00000 - "missing keyword"
    *Cause:
    *Action:
    Error at line:2 collumn:35

    Not quite sure what you mean by DDL. Expected results are that it prints customer ID, customer postcodes, product ID and product sale date. The postcode must begin M33 and the dates must be in March
    Last edited by Smush; 03-23-09 at 09:45.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Smush
    What I'm trying to do is is make an sql report that joins a customer table and a product table to produce one table that display customer purchases.
    There is one logical question: how would you tell which customer made any given puchase? There *shall* be a column identifying a customer (eg. his id) in purchase table. If there is no such column, it is impossible to map purchases to customers in other way than cartesian product (every purchase to every customer). Otherwise, do a (INNER) JOIN. Its syntax is described in SQL Reference, available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/.
    Quote Originally Posted by Smush
    I have managed to join them successfuly
    Seems a contradiction of your recent post:
    Quote Originally Posted by Smush
    also it doesn't seem to like:
    FROM customers c JOIN products p
    is that incorrect?
    Quote Originally Posted by Smush
    I am just stuggling with the LIKE function as these conditions are parts in a whole field... i.e. MAR is only the month of the entire date field.
    What is tha data type of PURCHASEDATE? If DATE, use condition which shammat already provided.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Smush
    Yes I get a pop-up saying:
    Error encountered
    ORA-00905: missing keyword
    00905. 00000 - "missing keyword"
    *Cause:
    *Action:
    Error at line:2 collumn:35
    Show us the full SELECT that you used when you got that error

    How does it know that 3 is the same as 'MAR' in the date field?
    Because March is the third month in the year (at least when using the gregorian calendar)

  8. #8
    Join Date
    Jan 2009
    Posts
    11
    Yes there is a customer ID in the products table too and yes the data type for the date is DATE. Ok I will give INNER JOIN a try.

    Yes I did contradict myself but didn't get the error until I started putting the LIKE function in.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by Smush
    How does it know that 3 is the same as 'MAR' in the date field? also it doesn't seem to like:
    FROM customers c JOIN products p
    is that incorrect?
    Yes, it is incorrect. JOIN requires an ON or USING clause e.g.
    Code:
    FROM customers  c JOIN products p ON p.customer_id = c.customer_id
    (BTW It seems unlikely to me that you will have a customer_id in the products table or a product_id in the customers table! Aren't we missing an intermediate table here - ORDER_LINES perhaps?)

  10. #10
    Join Date
    Jan 2009
    Posts
    11
    Sorry by products table I mean purchases so yes cystomer_id is in the purchases table...this sql isn't actually what I've got it's a general example of what I'm trying to achieve as what I'm trying to do is for a piece of coursework.

    should I use an ON or USING function or go for INNER JOIN... I don't think carteasian product will be of much use to me.

    and when applying a clause should I put it before the join or afterwards?
    Last edited by Smush; 03-23-09 at 13:22.

  11. #11
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Smush
    should I use an ON or USING function or go for INNER JOIN... I don't think carteasian product will be of much use to me.

    and when applying a clause should I put it before the join or afterwards?
    As this is only syntax (not logical) convention, it is already described in many places, so you shall be able to find it out e.g.
    Quote Originally Posted by flyboy
    in SQL Reference, available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
    By the way, this JOIN keyword is defined in ANSI syntax, so it is also available in non-Oracle databases - available in many other places.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by Smush
    should I use an ON or USING function or go for INNER JOIN... I don't think carteasian product will be of much use to me.

    and when applying a clause should I put it before the join or afterwards?
    I would normally use ON. USING works if both tables have the same name for the join columns. Examples:

    Code:
    SELECT emp.ename, dept.dname
    FROM   emp 
           JOIN dept ON dept.deptno = emp.deptno
    WHERE   dept.loc = 'LONDON';
    
    SELECT emp.ename, dept.dname
    FROM   emp 
           JOIN dept USING (deptno)
    WHERE   dept.loc = 'LONDON';

  13. #13
    Join Date
    Jan 2009
    Posts
    11
    Quote Originally Posted by andrewst
    I would normally use ON. USING works if both tables have the same name for the join columns. Examples:

    Code:
    SELECT emp.ename, dept.dname
    FROM   emp 
           JOIN dept ON dept.deptno = emp.deptno
    WHERE   dept.loc = 'LONDON';
    would emp be a table in your database because you use emp.ename but you also use FROM emp. does the emp before the period need to be a table name? If not I am getting confused with what you are doing here because where you have emp.ename I would have something like e.ename then FROM employees is that right? And where you have the WHERE could I use it with a LIKE function and the date extraction that a previous poster enlightend me with?

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by Smush
    would emp be a table in your database because you use emp.ename but you also use FROM emp. does the emp before the period need to be a table name? If not I am getting confused with what you are doing here because where you have emp.ename I would have something like e.ename then FROM employees is that right?
    If your table is employees then you can do:
    Code:
    SELECT employees.ename FROM employees
    or
    Code:
    SELECT e.ename FROM empoyees e
    In the second example, "e" is a table alias. In the first example, since I specified no alias a default alias of "employees" is assumed.
    And where you have the WHERE could I use it with a LIKE function and the date extraction that a previous poster enlightend me with?
    Yes indeedy - any WHERE-y stuff goes in the WHERE clause

  15. #15
    Join Date
    Jan 2009
    Posts
    11
    Ok, I have no errors now but there is no data being displayed, cannot see what I am missing. Can anyone help?

    SQL:
    SELECT customers.CUSTNO as "Customer ID", customers.CUSTNAME as "Customer Name", customers.CUSTPOSTCODE as "Postcode", purchases.pID as "Product ID", purchases.purchaseDATE as "Purchase Date"
    FROM purchases JOIN customers ON customers.custno = purchases.custno
    WHERE extract(month from purchases.purchasedate) = 3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •