Skip to content

MySQL query finding values in a comma separated string

I have a field COLORS (varchar(50)) in a my table SHIRTS that contains a comma delimited string such as 1,2,5,12,15,. Each number representing the available colors. When running the query select * from shirts where colors like ‘%1%’ to get all the red shirts (color=1), I also get the shirts whose …

Optimizing SELECT COUNT to EXISTS

I have a query to find certain customers from a table. There is an index on customerid, so the DB scans all rows with customerid = 22. Since the result is processed by checking whether the count returns zero or more than zero, how can I optimize the query? I.e. such that at the first customer row with amount …

DISTINCT for only one column

Let’s say I have the following query. How can I modify it so that it returns no duplicate Emails? In other words, when several rows contain the same email, I want the results to include only one of those rows (preferably the last one). Duplicates in other columns should be allowed. Clauses like DISTINCT…

MySQL Integer 0 vs NULL

When using integer columns is it better to have 0 or NULL to indicate no value. For example, if a table had a parent_id field and a particular entry had no parent, would you use 0 or NULL? I have …

Multiple strings in one SQL field

I am trying to revamp an order system for an e-commerce web site. The header table for the orders is tbl_orders, that’s working fine but there is another table underneath that (so to speak) called tbl_orderitems In this table tbl_orderitems I store an Id that refers to the items image but sometimes ther…