RE: Help with query

[Schwartz, Evelyn]

If every record in the listing table will have a corresponding record in the category 
table you may just include the category clause in with the rest.  
 
WHERE
    (a.ArrangerLname like '%$Criteria%' or
    p.PublisherName like '%$Criteria%' or
    c.ComposerLname like '%$Criteria%' or
    l.Title like '%$Criteria%' or
    l.CatalogNumber like '%$Criteria%')
AND
    l.PublisherID=p.PublisherID and
    l.ComposerID=c.ComposerID and
    l.ArrangerID=a.ArrangerID and
    l.CategoryID=o.CategoryID

        -----Original Message----- 
        From: Erich Beyrent [mailto:[EMAIL PROTECTED] 
        Sent: Fri 2/6/2004 9:00 AM 
        To: [EMAIL PROTECTED] 
        Cc: 
        Subject: RE: Help with query
        
        

        Hi Evelyn,
        
        How would I do that - would something like this be what you had in mind?
        
        left join categories o on o.CategoryID = l.CategoryID
        
        
        This goes in the WHERE clause, right?
        
        Thanks!
        
        -Erich-
        
        
        -----Original Message-----
        From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED]
        Sent: Friday, February 06, 2004 8:53 AM
        To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
        Subject: RE: Help with query
        
        You will need parentheses around the 'or' clauses of your where clause. 
        
        You also don't seem to join the categories table with any other tables.  If
        you don't join tables you will create what is called a 'cross product'
        query.  If table A has 10 rows and table B has 20 rows then querying A and B
        will return 200 rows (every row of A will be joined with every row of B!).
        
        
        -----Original Message-----
        From: Erich Beyrent [mailto:[EMAIL PROTECTED]
        Sent: Fri 2/6/2004 8:46 AM
        To: [EMAIL PROTECTED]
        Cc:
        Subject: Help with query
        Hi everyone,
        
        I am having a rough time with a query, which seems to be taking so long
        it hangs the systems.
        
        
        SELECT
            l.CatalogNumber,
            l.PDFLink,
            l.PDFName,
            l.Title,
            p.PublisherName,
            c.ComposerLname,
            a.ArrangerLname,
            l.Price,
            l.Description,
            o.Alias
        FROM
            listings l,
            publishers p,
            composers c,
            arrangers a,
            categories o
        WHERE
            a.ArrangerLname like '%$Criteria%' or
            p.PublisherName like '%$Criteria%' or
            c.ComposerLname like '%$Criteria%' or
            l.Title like '%$Criteria%' or
            l.CatalogNumber like '%$Criteria%'
        AND
            l.PublisherID=p.PublisherID and
            l.ComposerID=c.ComposerID and
            l.ArrangerID=a.ArrangerID
        ORDER BY
            o.Alias";
        
        
        How can I rewrite this query to be efficient (and functioning!) - I am
        fairly new to MySQL and could use lots of advice!
        
        Thanks!
        
        -Erich-
        
        
        
        --
        MySQL General Mailing List
        For list archives: http://lists.mysql.com/mysql
        To unsubscribe:
        http://lists.mysql.com/[EMAIL PROTECTED]