SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question SELECT statement from more than 1 table

    Looking for some help with a SELECT query please.

    I have two tables of data, each table has a reference column in it which is what matches the data.
    Table 1 contains product information and Table 2 contains discount information on the products.

    I am trying to set up a query which will only show items from Table 1 where a discount for that item is set in Table 2.
    I've had a look at some of the SQL JOIN statements but I can't get them to work correctly

    Can anyone help me with this please?

    Thanks in advance

  2. #2
    SitePoint Addict WolfShade's Avatar
    Join Date
    Mar 2014
    Location
    St. Louis, MO, USA
    Posts
    263
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Standard JOIN should get you only data where there is a match in both tables. It's the LEFT/RIGHT OUTER JOINS that will get all data even if no matching data is in the other table.

    May we see your query, so far?

    V/r,

    ^_^

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Off Topic:

    Quote Originally Posted by WolfShade View Post
    V/r
    wha?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict WolfShade's Avatar
    Join Date
    Mar 2014
    Location
    St. Louis, MO, USA
    Posts
    263
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I work in a DoD environment. "V/r" = "Very respectfully".

    V/r,

    ^_^

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Off Topic:

    DoD ?

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Off Topic:

    DoD ?
    Off Topic:

    Presumably Department of Defense (though there seem to be a load of alternates ) - they short hand/acronymize EVERYTHING - drives you nuts after a while.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #7
    SitePoint Addict WolfShade's Avatar
    Join Date
    Mar 2014
    Location
    St. Louis, MO, USA
    Posts
    263
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    Off Topic:

    Presumably Department of Defense (though there seem to be a load of alternates ) - they short hand/acronymize EVERYTHING - drives you nuts after a while.
    Yes, Department of Defense. And, yes, the military loves acronyms. Almost EVERYTHING has an acronym.

    V/r,

    ^_^

  8. #8
    SitePoint Addict WolfShade's Avatar
    Join Date
    Mar 2014
    Location
    St. Louis, MO, USA
    Posts
    263
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Follow up: Here's an idea of how much the military loves acronyms.

    http://www.dtic.mil/doctrine/dod_dictionary/

    V/r,

    ^_^

    UPDATE: I just realized that this is not the comprehensive list I thought it was. It's missing a few.

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Off Topic:

    Ahhh, NIGI. TYVM

  10. #10
    SitePoint Addict WolfShade's Avatar
    Join Date
    Mar 2014
    Location
    St. Louis, MO, USA
    Posts
    263
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Off Topic:

    Ahhh, NIGI. TYVM
    ROFL! YW.

    I just realized that the OP (sketchgal) has not returned. I hope we didn't chase her off. I apologize for hijacking the thread.

    V/r,

    ^_^

  11. #11
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by WolfShade View Post
    Yes, Department of Defense. And, yes, the military loves acronyms. Almost EVERYTHING has an acronym.

    V/r,

    ^_^
    Or bizarre terms - my favorite was Sneaker Net, which was for physically walking a disk from a computer on a secure network to a computer on a unsecure network and vice verse.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  12. #12
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by WolfShade View Post

    UPDATE: I just realized that this is not the comprehensive list I thought it was. It's missing a few.
    Yup - there are a handful that I worked for that aren't on that list.....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  13. #13
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, not scared off, just not had a chance to get back to this!
    Here now though, I'll have a look at the JOIN statement thats been suggested!
    Thanks so far guys.

  14. #14
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Brilliant! I've now got it to return the rows I need it to which is great. Only need to work out how I get it to count these rows now for pagination etc.

    Here's my query now:

    Code:
    $testquery = "SELECT Virtual_VIP_Offers.Reg, Virtual_VIP_Offers.OfferA, Virtual_VIP_Offers.OfferB, Virtual_VIP_Offers.OfferC, Virtual_VIP_Offers.OfferD, Virtual_VIP_Offers.OfferE, Virtual_VIP_Offers.OfferF, Virtual_VIP_Offers.Reserved, Used_Stock.Full_Registration, Used_Stock.Year, Used_Stock.Make, Used_Stock.Model, Used_Stock.Variant, Used_Stock.Picture_Refs, Used_Stock.Price
    FROM Virtual_VIP_Offers
    INNER JOIN Used_Stock
    ON Virtual_VIP_Offers.Reg=Used_Stock.Full_Registration WHERE 
    Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferA !='' AND Virtual_VIP_Offers.OfferA !='sold'
    OR
    Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferB !='' AND Virtual_VIP_Offers.OfferA !='sold'
    OR
    Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferC !='' AND Virtual_VIP_Offers.OfferA !='sold'
    OR
    Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferD !='' AND Virtual_VIP_Offers.OfferA !='sold'
    OR
    Virtual_VIP_Offers.Site IN ('123318', '123155') AND Virtual_VIP_Offers.OfferE !='' AND Virtual_VIP_Offers.OfferA !='sold'
    ";
    $qry_result = mysql_query($testquery) or die(mysql_error());
    Can anyone tell me how I would use this sort of count query for this please?

    Code:
    $firstcountquery = "SELECT COUNT(*) as Num FROM Used_Stock WHERE Feed_Id IN ('123318', '123155')";
    $total_results_start = mysql_result(mysql_query("$firstcountquery"),0);
    echo $total_results_start;
    any help greatly appreciated.

  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Were you the one responsible for having products support multiple offers by adding columns offerB, offerC, offerD, and offerE? The reason I ask is because the addition of those columns does not adhere to first normal form. The means of resolving the business problem of supporting multiple offers per product has introduced a quite apparent amatuer mistake and flaw in the database architecture. If you made that decision than I highly suggest taking the proper time to do things right. If that wasn't your doing than forget I said anything because we all have had to deal with other peoples poor decisions and make the best of a bad situation. However, from where I stand it looks to me like the requirement was products would only ever support a single offer and someone has hacked on multiple offer support due to a change in scope or feature enhancement. That way of adding multiple offer support if not amatuer is just sloppy and quite frankly unprofessional.
    The only code I hate more than my own is everyone else's.

  16. #16
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's the structure I have to work with, not my design I'm affraid. Is there a way to get a count based on this sort of query?

  17. #17
    SitePoint Addict WolfShade's Avatar
    Join Date
    Mar 2014
    Location
    St. Louis, MO, USA
    Posts
    263
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Most server-side solutions have that information already. For instance, ColdFusion <cfquery> has an attribute called "recordCount" that will tell you how many records were returned in the query.

  18. #18
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks WolfShade do you know if php has a similar attribute?

  19. #19
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sketchgal View Post
    It's the structure I have to work with, not my design I'm affraid. Is there a way to get a count based on this sort of query?
    Fair enough.

    Quote Originally Posted by sketchgal
    Thanks WolfShade do you know if php has a similar attribute?
    If the relationship between Virtual_VIP_Offers and Used_Stock is 1:1 SQL_CALC_FOUND_ROWS can be used. Otherwise duplicates need to be collapsed so the true number of rows shown client side match the result set. That would most likely be done by using a combination of grouping and subqueries.
    The only code I hate more than my own is everyone else's.

  20. #20
    SitePoint Addict WolfShade's Avatar
    Join Date
    Mar 2014
    Location
    St. Louis, MO, USA
    Posts
    263
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I'm pretty sure it does, but can't say for certain. Even if it didn't, you could always query the db, place the query object into an array, close the db connection, and use the length of the array as the number of returned records. I like doing it that way, just because it allows the connection to close before outputting the data.

    ^_^

  21. #21
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    I'm pretty sure it does, but can't say for certain. Even if it didn't, you could always query the db, place the query object into an array, close the db connection, and use the length of the array as the number of returned records. I like doing it that way, just because it allows the connection to close before outputting the data.
    That doesn't sound like a very scalable option. You would either run a second query to obtain just the count or use found_rows as explained. That is the correct way to do things. Simply collecting everything in memory and iterating over it in the application language to count and throw away is not a good solution.
    The only code I hate more than my own is everyone else's.


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
  •