Ecommerce Shopify WordPress Discussion

SELECTING with multiple WHERE conditions on same column

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying flags to a user ... ID contactid flag flag_type ----------------------------------- 118 99 Volunteer 1 119 99 Uploaded 2 120 100 Via Import 3 121 100 Volunteer 1 122 100 Uploaded 2 etc... in this case you'll see both contact 99 and 100 are flagged as both "Volunteer" and "Uploaded"... What I need to be able to do is return those contactid's ONLY that match multiple criteria entered via a search form...the contactid's have to match ALL chosen flags... in my head the SQL should look something like: SELECT contactid WHERE flag = 'Volunteer' AND flag = 'Uploaded'... but... that returns nothing... What am I doing wrong here?
Sometimes you can't see the wood for the trees :) Your original SQL .. SELECT contactid WHERE flag = 'Volunteer' AND flag = 'Uploaded'... Should be: SELECT contactid WHERE flag = 'Volunteer' OR flag = 'Uploaded'...

January 20, 2024

Use this: For example: select * from ACCOUNTS_DETAILS where ACCOUNT_ID=1001 union select * from ACCOUNTS_DETAILS where ACCOUNT_ID=1002

January 20, 2024

Change AND to OR. Simple mistake. Think of it like plain English, I want to select anything with that equals this or that.

January 20, 2024

select purpose.pname,company.cname from purpose Inner Join company on purpose.id=company.id where pname='Fever' and cname='ABC' in ( select mname from medication where mname like 'A%' order by mname );

January 20, 2024

AND will return you an answer only when both volunteer and uploaded are present in your column. Otherwise it will return null value... try using OR in your statement ... SELECT contactid WHERE flag = 'Volunteer' OR flag = 'Uploaded'

January 20, 2024

something like this should work for you SELECT * FROM `product_options` GROUP BY product_id HAVING COUNT(option_id IN (1,2,3) OR NULL) > 0 AND COUNT(option_id IN (7) OR NULL) > 0

January 20, 2024

SELECT contactid, Count(*) FROM <YOUR_TABLE> WHERE flag in ('Volunteer','Uploaded') GROUP BY contactid HAVING count(*)>1;

January 20, 2024

Try to use this alternate query: SELECT A.CONTACTID FROM (SELECT CONTACTID FROM TESTTBL WHERE FLAG = 'VOLUNTEER')A , (SELECT CONTACTID FROM TESTTBL WHERE FLAG = 'UPLOADED') B WHERE A.CONTACTID = B.CONTACTID;

January 20, 2024

can't really see your table, but flag cannot be both 'Volunteer' and 'Uploaded'. If you have multiple values in a column, you can use WHERE flag LIKE "%Volunteer%" AND flag LIKE "%UPLOADED%" not really applicable seeing the formatted table.

January 20, 2024

Consider using INTERSECT like this: SELECT contactid WHERE flag = 'Volunteer' INTERSECT SELECT contactid WHERE flag = 'Uploaded' I think it it the most logistic solution.

January 20, 2024

Use: SELECT t.contactid FROM YOUR_TABLE t WHERE flag IN ('Volunteer', 'Uploaded') GROUP BY t.contactid HAVING COUNT(DISTINCT t.flag) = 2 The key thing is that the counting of t.flag needs to equal the number of arguments in the IN clause. The use of COUNT(DISTINCT t.flag) is in case there isn't a unique constraint on the combination of contactid and flag -- if there's no chance of duplicates you can omit the DISTINCT from the query: SELECT t.contactid FROM YOUR_TABLE t WHERE flag IN ('Volunteer', 'Uploaded') GROUP BY t.contactid HAVING COUNT(t.flag) = 2

January 20, 2024

You can either use GROUP BY and HAVING COUNT(*) = _: SELECT contact_id FROM your_table WHERE flag IN ('Volunteer', 'Uploaded', ...) GROUP BY contact_id HAVING COUNT(*) = 2 -- // must match number in the WHERE flag IN (...) list (assuming contact_id, flag is unique). Or use joins: SELECT T1.contact_id FROM your_table T1 JOIN your_table T2 ON T1.contact_id = T2.contact_id AND T2.flag = 'Uploaded' -- // more joins if necessary WHERE T1.flag = 'Volunteer' If the list of flags is very long and there are lots of matches the first is probably faster. If the list of flags is short and there are few matches, you will probably find that the second is faster. If performance is a concern try testing both on your data to see which works best.

January 20, 2024

TurboCommerce make the better internet purchasing globaly

Turbo Multi-language Translator

Make the better internet purchasing globaly

Turbosify SEO Speed Booster

5.0 (7) Free plan available
Get better conversions by improving store loading speed Installed

Turbo Multi-language Chat - AI Customer service in one hand

TurboCommerce make the better internet purchasing globaly
Our products

The help you need, when you need it

App by Turbo Engine

3 apps • 5.0 average rating

Turbosify Speed Booster

5.0 (7)
Get better conversions by optimizing shopify store Google page speed Installed

Turbosify Translator for Wordpress Woocommerce

5.0 (74) Free Wordpress Woocommerce Plugin
Translate your wordpress website to multiple language within 1 click, no configuration needed, no No technical required

Grow your business here

Whether you want to sell products down the street or around the world, we have all the tools you need.