Results 1 to 5 of 5

Thread: SQL Problem

  1. #1
    Join Date
    Sep 2002
    Posts
    4

    Unanswered: SQL Problem

    Hi

    Can anybody help with this one ->

    Using the SQL statements on the pubs database

    Select *
    from stores left join sales
    on stores.stor_id = sales.stor_id
    where sales.title_id = 'PS2091' OR sales.title_id = 'BU1032'

    This is fine, works perfectly and returns all the 'stores' records with either 'PS2091' or 'BU1032' in the sales table. But if I only want records with BOTH 'PS2091' and 'BU1032' then surely....

    Select *
    from stores left join sales
    on stores.stor_id = sales.stor_id
    where sales.title_id = 'PS2091' AND sales.title_id = 'BU1032'

    but no joy - this returns nothing at all!?!?!??! How can I (in one SQL statement) get the records in the 'stores' table for which there are records in the 'sales' table for both 'PS2091' and 'BU1032'.

    There should be one record returned which is the store name 'Eric the Read Books'. I am sure this is simply but can't seem to find a solution.

    Can anybody Help?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "then surely..."?

    x=1 AND x=2 sounds right, but not if you think about it in terms of the x value of each row

    what you want is the existence of two rows, one of each
    Code:
    select * 
      from stores 
    left join sales 
        on stores.stor_id = sales.stor_id
     where 2 = 
        ( select count(*)
            from sales
           where store.id = stores.stor_id
             and ( title_id = 'PS2091' 
                or title_id = 'BU1032' )
        )
    the subselect is correlated, looking at all rows for each store, counting the ones that meet any of the individual criteria -- if this count is 2, then that store qualifies, and all of its sales rows are selected in the outer query

    i'm not familiar with the pubs database and am assuming that there's at most one sales row per title per store, otherwise the query won't work right

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Sep 2002
    Posts
    4
    "then surely..."?

    OK Bad choice of words.

    Thanks Ruby - your solution worked at treat. Your help is appreciated.

    by the way - just out of interest - the above (my original SQL post actually worked using a JET database and not SQL Server!! - Now why is that?).

    Thanks again - you saved the day!!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy, not ruby -- i'm definitely a guy, eh

    anyhow, your original AND query may have run, but it would not have found any sales rows where that particular row's title_id value is two different values at the same time (think about it)

    consequently, no sales rows will ever match, BUT, since it's a left outer join, you get back all the stores where no sales rows match that impossible condition, which should be all stores!!

    i'm fairly certain about this, although i haven't tested it

    rudy

  5. #5
    Join Date
    Sep 2002
    Posts
    4
    Sorry Rudy...I thought Ruby was a bit strange!!

    You are right - my original AND query did run and return no results in SQL Server but did return the correct results in MS Access (exactly the same query).

    I implemented the new code (that you provided) and this works perfectly and returns the same set of results in SQL Server that is returned in Access with my original AND query.

    The actual query in my application is loads more complicated as it has six join tables and also queries criteria in the master table as it well. It also has nothing to do with sales or stores either - just used this as an example of the problem. Ultimately - It is a very very large SQL statement that is generated in the end. I just cut it down to what I posted initially on the forum to simplify it for the post, as I knew this was the bit that wasn't working (six times over).

    I know exactly what your saying that the query is 'impossible' as it is asking one value to be different values, which is completely not possible which is why it returned no results.

    I orginally developed the application that is using this is VB6 + Access and then transposed it to an SQL Server back-end which is where the problem arose. But all working now thanks to you.

    Cheers again

    Andy.

Posting Permissions

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