SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    Minnesota
    Posts
    317
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query help. Results from two tables.

    This should be easy, but for some reason I'm having a tough time getting it to work.

    I have two tables. One contains all registered shoppers and their info. The other contains purchase receipt information. Both tables have the column shopper_id in common.

    Now, I'm trying to get a list of shoppers that have registered, but have not ordered. I tried this, but it doesn't work:

    Code:
    dim strSQL
    strSQL = "SELECT DISTINCT S.date_created, R.total, R.order_id, S.name, S.email, S.last_visit, S.shopper_id " & _
             "  FROM STORE_SHOPPER S, STORE_RECEIPT R " & _
    		 "  WHERE S.shopper_id <> R.shopper_id " & _
    		 "  AND datediff(day, date_entered, getdate()) <= 20 " & _	 
    		 "  ORDER BY S.date_created DESC"
    Set results = dbConn.Execute(strSQL)
    Any help would be appreciated, thanks!
    Keomed.com Webmaster
    www.keomed.com

  2. #2
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try:

    Code:
    SELECT DISTINCT S.date_created, R.total, R.order_id, S.name, S.email, S.last_visit, S.shopper_id
        FROM STORE_SHOPPER S
        LEFT JOIN STORE_RECEIPT R
            ON S.S.shopper_id = R.shopper_id
        WHERE R.shopper_id IS NULL

  3. #3
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    Minnesota
    Posts
    317
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried it, but didn't work. Didn't return any results.

    The difficult part is, that I'm trying to return shopper's that do not have a order_id in the store_receipt table.

    S.shopper_id = R.shopper_id
    This R.shopper_id are people that have ordered and thus have a order_id. Wouldn't equaling it to S.shopper_id only return users that have a order id?

    Thanks
    Keomed.com Webmaster
    www.keomed.com

  4. #4
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ooops. just noticed an error in my code. had S.S.shopper_id. trying again...

    Code:
    SELECT DISTINCT S.date_created, R.total, R.order_id, S.name, S.email, S.last_visit, S.shopper_id
        FROM STORE_SHOPPER S
        LEFT JOIN STORE_RECEIPT R
            ON S.shopper_id = R.shopper_id
        WHERE R.shopper_id IS NULL
    i think this *should* work. what database engine are you using?

  5. #5
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    Minnesota
    Posts
    317
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I did catch that error the first time.
    This is what I have now:

    Code:
    dim strSQL
    strSQL = "SELECT DISTINCT R.date_entered, R.order_id, S.name, S.email, R.total, S.last_visit, S.shopper_id, S.date_created " & _
             "  FROM STORE_SHOPPER S LEFT JOIN STORE_RECEIPT R ON S.shopper_id = R.shopper_id " & _
    		 "  WHERE R.shopper_id IS NULL " & _
    		 "  AND datediff(day, date_entered, getdate()) <= 70 " & _
    		 "  ORDER BY S.date_created DESC"
    Set results = dbConn.Execute(strSQL)
    But I'm still getting 0 results.
    Keomed.com Webmaster
    www.keomed.com

  6. #6
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you run your query in Access to see the results?

  7. #7
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    Minnesota
    Posts
    317
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah-ha! Figured out what was causing the problem!
    It was the datediff(day, date_entered, getdate()) part because it was asking for the date_entered from the STORE_RECEIPT table!!

    Sweet! Thanks for your help guys. Sorry about that.
    Last edited by Hideki; Apr 5, 2002 at 15:08.
    Keomed.com Webmaster
    www.keomed.com

  8. #8
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you are explaining it well, but whenever I get stuck, I try to run my queries in Access to get a more visual idea of what is going on. It's easier to adjust and I get faster feedback.


Bookmarks

Posting Permissions

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