SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 30
  1. #1
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Searching two separate db's and returning 1 result

    Hello All!

    I'll do my best to describe my situation, I know posting code is always best but for this situation I'm not sure it would help.....?

    I have two databases.... let's call them db1 & db2

    each of these db's has a product table.

    db1's product table is manufacturer products.

    db2's product table is input by the site owner.

    each of these product tables is identical in structure (and the joins are identical also)


    I need to build a search that searches both db's and returns one result set of products by search relevance level.


    I'm thinking one query to each table, somehow combine the queries and then run the query on the results.....??? But I know this can't be right... can it...?

    I'm open to any and all suggestions and/or guidance on the best methodology to make this search process as quickly and accurate as possible.

    I already have both searches working, but I end up with two result sets. I could combine these somehow in php, but I feel that I would loose the search relevancy....?

    Thanks in advance for any guidance you may be able to provide.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rustybuddy View Post
    I'm thinking one query to each table, somehow combine the queries and then run the query on the results.....??? But I know this can't be right... can it...?
    yep, it can

    i'd show you how, but my version of Microsoft® CrystalBall© just died and i can't see your queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yep, it can

    i'd show you how, but my version of Microsoft® CrystalBall© just died and i can't see your queries
    Blue Screen Of Death?

    Back on topic, to the OP, for people to help they need to be able to see the structure of the tables that you want to get data from and what fields/data you want to get from them in the query.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, here goes. These queries are fairly dynamic as they are assembled on the fly.

    Code:
    SELECT inventory.id 
      , inventory.folderID 
      , inventory.title 
      , list_brands.name AS manuName 
      , list_engineType.name AS engineType 
      , inventory_images.fileName AS mainImageFileName 
    
     FROM inventory 
    
    INNER JOIN list_brands ON list_brands.id = inventory.manu 
    INNER JOIN list_engineType ON list_engineType.id = inventory.engineConfig
    INNER JOIN list_fuel ON list_fuel.id = inventory.fuelType 
    INNER JOIN inventory_images ON inventory_images.folderID = inventory.folderID 
       AND inventory_images.isMainImage = 1 
    
    WHERE 1 
      AND inventory.manu = '142' 
      LIMIT 0, 30
    I removed quite a bit from the SELECT statement just to keep this on the same page. Both queries are basically the same, they just query two different databases.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do both require LIMIT 30? i mean, are you looking for the top 60 or something?

    also, are both databases on the same server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, I need the top 30 results total (to display in search result on page), right now I get 60 overall.

    Yes both db's are on the same server.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT dt.id 
         , dt.folderID 
         , dt.title 
         , dt.manuName 
         , dt.engineType 
         , dt.mainImageFileName 
      FROM ( query one without LIMIT
             UNION ALL
             query two without LIMIT
           ) AS dt
    ORDER 
        BY something LIMIT 30
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I think I'm beginning to grasp the concept but am a bit confused about the FROM statement.

    FROM (query one without LIMIT
    ..........)

    What am I substituting for 'query one'?

    Also would assume that I just put my WHERE before ORDER?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rustybuddy View Post
    What am I substituting for 'query one'?
    the entire query from post #4 including the WHERE clause but without the LIMIT

    same for query two (remember? you said you have two queries?)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy,
    I'm having a tough time grasping this, sorry.... Would there be an example of this situation in your book by chance? Any chance you know of a tut site that has a tut on this by chance? I've googled and read quite a bit on the subject but still not quite sure......

    Again, I'm still a noob to SQL Sorry I'm not grasping it yet....

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    grasping what?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rustybuddy View Post
    Rudy,
    I'm having a tough time grasping this, sorry.... Would there be an example of this situation in your book by chance? Any chance you know of a tut site that has a tut on this by chance? I've googled and read quite a bit on the subject but still not quite sure......

    Again, I'm still a noob to SQL Sorry I'm not grasping it yet....
    Union queries are covered in Rudy's book, pages 64-67.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  13. #13
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    same for query two (remember? you said you have two queries?)
    yes, I have two queries that are working. So I have two queries that return a result set or a record set, (I'm using peardb abstraction layer, don't think I mentioned that, not sure it matters.) So each db connection is an object.

    So, are you saying that I run each of the queries and then run a query on the result of those queries? Or are you saying that I run one query on both db's at the same time? I guess this is what I don't grasp.....?

    Thanks again for all your help, time and most of all your book!!

  14. #14
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT dt.id 
         , dt.folderID 
         , dt.title 
         , dt.manuName 
         , dt.engineType 
         , dt.mainImageFileName 
      FROM ( query one without LIMIT
             UNION ALL
             query two without LIMIT
           ) AS dt
    ORDER 
        BY something LIMIT 30
    This is how it works (Rudy, please correct me if i get this wrong)


    1. Query One is run and generates its own results set
    2. Query Two is run and generates its own results set
    3. The Union All then runs and combines the the results sets of Query One and Query Two, keeping all duplicate rows.
    4. The main query then runs, operating on the combined result set created by the Union All.

    The diagram on page 66 of Rudy's book shows how Union queries work and the textbox at the top of page 67 explains about the difference between Union and Union All queries.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    This is how it works (Rudy, please correct me if i get this wrong)
    that's correct, and nicely explained
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so would I populate the FROM statement with this?

    http://pear.php.net/manual/en/packag....db-result.php

    Here are the other options.....?
    http://pear.php.net/package/DB/docs

    or am I missing the point?

  17. #17
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe able to create a third table, which is a merge of the other two.

    http://dev.mysql.com/doc/refman/5.0/...ge-engine.html

  18. #18
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rustybuddy View Post
    Ok, here goes. These queries are fairly dynamic as they are assembled on the fly.

    Code:
    SELECT inventory.id 
      , inventory.folderID 
      , inventory.title 
      , list_brands.name AS manuName 
      , list_engineType.name AS engineType 
      , inventory_images.fileName AS mainImageFileName 
    
     FROM inventory 
    
    INNER JOIN list_brands ON list_brands.id = inventory.manu 
    INNER JOIN list_engineType ON list_engineType.id = inventory.engineConfig
    INNER JOIN list_fuel ON list_fuel.id = inventory.fuelType 
    INNER JOIN inventory_images ON inventory_images.folderID = inventory.folderID 
       AND inventory_images.isMainImage = 1 
    
    WHERE 1 
      AND inventory.manu = '142' 
      LIMIT 0, 30
    I removed quite a bit from the SELECT statement just to keep this on the same page. Both queries are basically the same, they just query two different databases.
    Are the tables identical in both dbs (not necessarity in terms of the data in them but their structure ie, field names, field types, etc)?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  19. #19
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Are the tables identical in both dbs (not necessarity in terms of the data in them but their structure ie, field names, field types, etc)?
    Yes the tables are identical in structure.

  20. #20
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry guys, I'm still not quite getting this, I'm very confused.

    SELECT dt.id
    , dt.folderID
    , dt.title
    , dt.manuName
    , dt.engineType
    , dt.mainImageFileName
    FROM ( query one without LIMIT
    UNION ALL
    query two without LIMIT
    ) AS dt
    ORDER
    BY something LIMIT 30
    Do I put the result set from query one and two in here or do I put the entire query statement (ie, SELECT inv.folderID......FROM....etc)

    Thanks again for all ya'll help, and I apologize if I'm being stupid about this.

  21. #21
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, here is my query and it is running without errors but is returning an empty result set...?

    Code:
    SELECT dt.id 
    	, dt.folderID 
    	, dt.title 
    	, dt.new 
    	, dt.manu 
    	, dt.modelYear 
    	, dt.special 
    	, dt.shortDesc 
    	, dt.loaFeet 
    	, dt.loaInches 
    	, dt.beamFeet 
    	, dt.beamInches 
    	, dt.engineTypeNumber 
    	, dt.hp 
    	, dt.hours 
    	, dt.fuelTypeNumber 
    	, dt.fuelCap 
    	, dt.waterCap 
    	, dt.price 
    	, dt.standards 
    	, dt.options 
    	, dt.uploadedBySDR 
    	, dt.stockNumber 
    	, dt.sold 
    	, dt.manuName 
    	, dt.engineType 
    	, dt.fuel 
    	, dt.mainImageFileName 
    	, dt.mainImageTitle 
    	, dt.mainImageAlt 
    
    FROM ( SELECT 
    	  inventory.id 
    	, inventory.folderID 
    	, inventory.title 
    	, inventory.new 
    	, inventory.manu 
    	, inventory.modelYear 
    	, inventory.special 
    	, inventory.shortDesc 
    	, inventory.loaFeet 
    	, inventory.loaInches 
    	, inventory.beamFeet 
    	, inventory.beamInches 
    	, inventory.engineConfig AS engineTypeNumber 
    	, inventory.hp 
    	, inventory.hours 
    	, inventory.fuelType AS fuelTypeNumber 
    	, inventory.fuelCap 
    	, inventory.waterCap 
    	, inventory.price 
    	, inventory.standards 
    	, inventory.options 
    	, inventory.uploadedBySDR 
    	, inventory.stockNumber 
    	, inventory.sold 
    	, list_brands.name AS manuName 
    	, list_engineType.name AS engineType 
    	, list_fuel.name AS fuel 
    	, inventory_images.fileName AS mainImageFileName 
    	, inventory_images.title AS mainImageTitle 
    	, inventory_images.alt AS mainImageAlt 
    
    FROM sitedone_dnr.inventory 
    	INNER JOIN list_brands ON list_brands.id = inventory.manu 
    	INNER JOIN list_engineType ON list_engineType.id = inventory.engineConfig 
    	INNER JOIN list_fuel ON list_fuel.id = inventory.fuelType 
    	INNER JOIN inventory_images ON inventory_images.folderID = inventory.folderID AND inventory_images.isMainImage = 1 
    
    WHERE 1 AND inventory.manu = '142' 
    
    UNION ALL 
    
    SELECT 
    	  inventory.id 
    	, inventory.folderID 
    	, inventory.title 
    	, inventory.new 
    	, inventory.manu 
    	, inventory.modelYear 
    	, inventory.special 
    	, inventory.shortDesc 
    	, inventory.loaFeet 
    	, inventory.loaInches 
    	, inventory.beamFeet 
    	, inventory.beamInches 
    	, inventory.engineConfig AS engineTypeNumber 
    	, inventory.hp 
    	, inventory.hours 
    	, inventory.fuelType AS fuelTypeNumber 
    	, inventory.fuelCap 
    	,  inventory.waterCap 
    	, inventory.price 
    	, inventory.standards 
    	, inventory.options 
    	, inventory.uploadedBySDR 
    	, inventory.stockNumber 
    	, inventory.sold 
    	, list_brands.name AS manuName 
    	, list_engineType.name AS engineType 
    	, list_fuel.name AS fuel 
    	, inventory_images.fileName AS mainImageFileName 
    	, inventory_images.title AS mainImageTitle 
    	, inventory_images.alt AS mainImageAlt 
    
    FROM sitedone_dealer.inventory 
    	INNER JOIN list_brands ON list_brands.id = inventory.manu 
    	INNER JOIN list_engineType ON list_engineType.id = inventory.engineConfig 
    	INNER JOIN list_fuel ON list_fuel.id = inventory.fuelType 
    	INNER JOIN inventory_images ON inventory_images.folderID = inventory.folderID AND inventory_images.isMainImage = 1 
    
    WHERE 1 AND inventory.manu = '142' ) 
    
    AS dt LIMIT 30
    Any ideas as to where this could be going wrong?

  22. #22
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so I fiddled with this query until I'm getting some results and they appear to be correct. But I'm not sure if this query is still correct or the best way to accomplish what I'm attempting. Open to any ideas or suggestions. Thanks again for all the help in this thread, sorry I was so slow catching on.

    Code:
    SELECT dt.id 
    	, dt.folderID 
    	, dt.title 
    	, dt.new 
    	, dt.manu 
    	, dt.modelYear 
    	, dt.special 
    	, dt.shortDesc 
    	, dt.loaFeet 
    	, dt.loaInches 
    	, dt.beamFeet 
    	, dt.beamInches 
    	, dt.engineTypeNumber 
    	, dt.hp 
    	, dt.hours 
    	, dt.fuelTypeNumber 
    	, dt.fuelCap 
    	, dt.waterCap 
    	, dt.price 
    	, dt.standards 
    	, dt.options 
    	, dt.uploadedBySDR 
    	, dt.stockNumber 
    	, dt.sold 
    	, dt.manuName 
    	, dt.engineType 
    	, dt.fuel 
    	, dt.mainImageFileName 
    	, dt.mainImageTitle 
    	, dt.mainImageAlt 
    
    FROM ( SELECT 
    	  sitedone_dnr.inventory.id 
    	, sitedone_dnr.inventory.folderID 
    	, sitedone_dnr.inventory.title 
    	, sitedone_dnr.inventory.new 
    	, sitedone_dnr.inventory.manu 
    	, sitedone_dnr.inventory.modelYear 
    	, sitedone_dnr.inventory.special 
    	, sitedone_dnr.inventory.shortDesc 
    	, sitedone_dnr.inventory.loaFeet 
    	, sitedone_dnr.inventory.loaInches 
    	, sitedone_dnr.inventory.beamFeet 
    	, sitedone_dnr.inventory.beamInches 
    	, sitedone_dnr.inventory.engineConfig AS engineTypeNumber 
    	, sitedone_dnr.inventory.hp 
    	, sitedone_dnr.inventory.hours 
    	, sitedone_dnr.inventory.fuelType AS fuelTypeNumber 
    	, sitedone_dnr.inventory.fuelCap 
    	, sitedone_dnr.inventory.waterCap 
    	, sitedone_dnr.inventory.price 
    	, sitedone_dnr.inventory.standards 
    	, sitedone_dnr.inventory.options 
    	, sitedone_dnr.inventory.uploadedBySDR 
    	, sitedone_dnr.inventory.stockNumber 
    	, sitedone_dnr.inventory.sold 
    	, sitedone_dnr.list_brands.name AS manuName 
    	, sitedone_dnr.list_engineType.name AS engineType 
    	, sitedone_dnr.list_fuel.name AS fuel 
    	, sitedone_dnr.inventory_images.fileName AS mainImageFileName 
    	, sitedone_dnr.inventory_images.title AS mainImageTitle 
    	, sitedone_dnr.inventory_images.alt AS mainImageAlt 
    
    FROM sitedone_dnr.inventory 
    	INNER JOIN sitedone_dnr.list_brands ON sitedone_dnr.list_brands.id = sitedone_dnr.inventory.manu 
    	INNER JOIN sitedone_dnr.list_engineType ON sitedone_dnr.list_engineType.id = sitedone_dnr.inventory.engineConfig 
    	INNER JOIN sitedone_dnr.list_fuel ON sitedone_dnr.list_fuel.id = sitedone_dnr.inventory.fuelType 
    	INNER JOIN sitedone_dnr.inventory_images ON sitedone_dnr.inventory_images.folderID = sitedone_dnr.inventory.folderID AND sitedone_dnr.inventory_images.isMainImage = 1 
    
    WHERE 1 AND inventory.manu = '142'
    
    UNION ALL 
    
    SELECT 
    	  sitedone_dealer.inventory.id 
    	, sitedone_dealer.inventory.folderID 
    	, sitedone_dealer.inventory.title 
    	, sitedone_dealer.inventory.new 
    	, sitedone_dealer.inventory.manu 
    	, sitedone_dealer.inventory.modelYear 
    	, sitedone_dealer.inventory.special 
    	, sitedone_dealer.inventory.shortDesc 
    	, sitedone_dealer.inventory.loaFeet 
    	, sitedone_dealer.inventory.loaInches 
    	, sitedone_dealer.inventory.beamFeet 
    	, sitedone_dealer.inventory.beamInches 
    	, sitedone_dealer.inventory.engineConfig AS engineTypeNumber 
    	, sitedone_dealer.inventory.hp 
    	, sitedone_dealer.inventory.hours 
    	, sitedone_dealer.inventory.fuelType AS fuelTypeNumber 
    	, sitedone_dealer.inventory.fuelCap 
    	,  sitedone_dealer.inventory.waterCap 
    	, sitedone_dealer.inventory.price 
    	, sitedone_dealer.inventory.standards 
    	, sitedone_dealer.inventory.options 
    	, sitedone_dealer.inventory.uploadedBySDR 
    	, sitedone_dealer.inventory.stockNumber 
    	, sitedone_dealer.inventory.sold 
    	, sitedone_dealer.list_brands.name AS manuName 
    	, sitedone_dealer.list_engineType.name AS engineType 
    	, sitedone_dealer.list_fuel.name AS fuel 
    	, sitedone_dealer.inventory_images.fileName AS mainImageFileName 
    	, sitedone_dealer.inventory_images.title AS mainImageTitle 
    	, sitedone_dealer.inventory_images.alt AS mainImageAlt 
    
    FROM sitedone_dealer.inventory 
    	INNER JOIN sitedone_dealer.list_brands ON sitedone_dealer.list_brands.id = sitedone_dealer.inventory.manu 
    	INNER JOIN sitedone_dealer.list_engineType ON sitedone_dealer.list_engineType.id = sitedone_dealer.inventory.engineConfig 
    	INNER JOIN sitedone_dealer.list_fuel ON sitedone_dealer.list_fuel.id = inventory.fuelType 
    	INNER JOIN sitedone_dealer.inventory_images ON sitedone_dealer.inventory_images.folderID = sitedone_dealer.inventory.folderID AND sitedone_dealer.inventory_images.isMainImage = 1 
    
    WHERE 1  AND inventory.manu = '142') 
    
    AS dt LIMIT 30
    Is it necessary to include the db name on every reference? (ie, sitedone_dnr.inventory.id )? This really hampers the ability of my script to create the query on the fly.

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rustybuddy View Post
    Any ideas as to where this could be going wrong?
    it's gotta be the data

    sometimes when you write an INNER JOIN, if you expect the tables to be linked by certain columns, they aren't

    other times, it's the WHERE conditions that filter everything out

    the way to test these hypotheses is to break down the queries to their individual tables

    in other words, start with one of the subqueries, e.g.
    Code:
    SELECT COUNT(*) AS rows
      FROM sitedone_dnr.inventory
     WHERE inventory.manu = '142'
    if this returns the expected number of rows, join to the next table --
    Code:
     
    SELECT COUNT(*) AS rows
      FROM sitedone_dnr.inventory
    INNER 
      JOIN list_brands 
        ON list_brands.id = inventory.manu 
     WHERE inventory.manu = '142'
    and keep adding joins until you hit 0 rows returned, that will tell you which join is the troublemaker
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, I guess we were both posting at the same time. Could you see my last response above yours?

    Is it necessary to include the db name on every reference? (ie, sitedone_dnr.inventory.id )? This really hampers the ability of my script to create the query on the fly. Is there a way around having to do this? It seems that using the db names is the only way I can get the correct results.

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if the tables are in different databases, then yes, you do need to qualify at least one of them with the appropriate database name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •