Ed Joe Ed Joe -4 years ago 101
SQL Question

sql IN reverse usage

I am trying to show products belongs to that location but I failed.

db_products.locationId stores id's string like 1,2,5,9

:locationId stores location id like 5 I send this variable as parameter to sql query.

SELECT db_products.*,
db_assets.path
FROM db_products INNER JOIN
db_assets ON db_assets.guid = db_products.guid
WHERE db_products.pcId = 1 AND
:locationId IN (db_products.locationId)
ORDER BY db_products.id

Answer Source

Fix your data structure! Do not store integers as strings in a list. Why not?

  • Integers should be stored as numbers, not strings.
  • SQL has poor string handling.
  • Foreign key relationships should be properly declared.
  • The resulting queries cannot make use of indexes.
  • SQL has a great data structure for storing lists. It is called "table".

So, use a junction table. You can look it up.

Sometimes, we are stuck with other people's really bad design decisions. In this case, MySQL has find_in_set():

SELECT p.*, a.path 
FROM db_products p INNER JOIN 
     db_assets a
     ON a.guid = p.guid 
WHERE p.pcId = 1  AND
      FIND_IN_SET(:locationId, p.locationId) > 0
ORDER BY p.id;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download