0

I want to take some records from two tables (products, categories) and actually retrieve the category name from the categories table according to the category id in the products table. The categories table, however, also has a field (COMID) other than the foreign key (CODEID) to the products table, so I want to retrieve the categories where that other field has a specific value.

So, I wrote the following two queries, which both work, and produce the exact same results, where the only difference is the placement of the condition of that other field... In the first case I put it in the general WHERE clause, whereas in the second case I put it in the ON clause of the INNER JOIN part of the query...

$sql = 'SELECT prod.DESCRIPTION, cat1.DESCR, prod.ICTID, prod.IGSID, prod.IGPID
        FROM MATERIAL AS prod
        INNER JOIN ITEMCATEGORY AS cat1 ON cat1.CODEID = prod.ICTID
        WHERE prod.ISACTIVE = 1 AND cat1.COMID = 12';

And

$sql = 'SELECT prod.DESCRIPTION, cat1.DESCR, prod.ICTID, prod.IGSID, prod.IGPID
        FROM MATERIAL AS prod
        INNER JOIN ITEMCATEGORY AS cat1 ON cat1.CODEID = prod.ICTID AND cat1.COMID = 12
        WHERE prod.ISACTIVE = 1';

Is there such a thing as a more correct approach in the above situation?

Thanks a lot in advance.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Faye D.
  • 833
  • 1
  • 3
  • 16
  • 2
    For an `inner` join, either way is correct. `Left` joins **must** use method 2, therefore I always do that. Some always do method 1 for `inner`. Purely preference – Charlieface Sep 17 '21 at 00:57
  • So, in my case where I realized that in fact I needed a LEFT JOIN instead of an INNER JOIN, it worked only by chance, as I ended up using the 2nd method! Ah, I had luck on my side! Thanks a lot! Though you should have written your reply as a separate answer, so that I could give you credit for it! – Faye D. Sep 17 '21 at 01:08
  • 2
    See also https://stackoverflow.com/questions/40093809/why-is-my-t-sql-left-join-not-working/40093841 there are many other posts on [so] about it. I would vote to close as duplicate, but just retracted my "opinion" close vote – Charlieface Sep 17 '21 at 01:10
  • Sometimes it's amazing what you can find when you can think of the right keywords for your search! Thanks a lot! Yup, this is definitely a duplicate case of the one you mentioned. But honestly I would never be able to find it myself! – Faye D. Sep 17 '21 at 01:15
  • @DaleK I deleted the image per your suggestion, but about the tags, both queries are identical in MySQL and SQL Server, that's why I used both tags. In fact, the project I'm using this query in retrieves data from an SQL server in PHP using `sqlsrv_`commands. – Faye D. Sep 17 '21 at 01:32
  • Fair enough! I removed MySQL tag. Afterall I used both tags to get more chances for a reply, using people from both _realms_! Now that I got the reply I was looking for, it doesn't really matter to me anymore about the tags! :) – Faye D. Sep 17 '21 at 01:35
  • 1
    For future reference, what generally happens is both groups of experts get annoyed because you haven't tagged the specific RDBMS. And you won't normally know in advance whether the best solution will be generic or specific to your RDBMS. And people who are experts in both will monitor the "SQL" tag rather than a specific RDBMS. – Dale K Sep 17 '21 at 01:38

0 Answers0