Conditional Select in MySQL

Conditional Select in MySQL

I am working on a mysql query and its giving me headache!

The Scenario:

I am building a website where people can select industries they are interested in (NOTIFY_INDUSTRY). I join the selected values and store in a database field.

Example: a member selects agriculture (id = 9) and oil and gas (id = 13). I join them as 9-13 and store in the database. Users can select several industries, not limited to two.

Also, members can select an industry (COMPANY_INDUSTRY) it belongs in assuming Information Technology which is stored in the database too.

Sample table (members):    ID  EMAIL  COMPANY_NAME  COMPANY_INDUSTRY  NOTIFY_INDUSTRY  

The problem:

When a new user registers on the website, mail (the mails are sent on daily basis) is sent to existing users who have the new user's industry (COMPANY_INDUSTRY) as one of their interested industries (NOTIFY_INDUSTRY).

What i have done:

$sql="select id, email        from members        where notify_industry in (          select company_industry          from members          where datediff($today, date_activated) <= 1)"  

This does not select the right members and i do not know the right way to go about it

EDIT - Exact Problem with current output:

Does not return any row, even when it should.

Assuming the new user's company_industry is 9, and there is an existing user with notify_industry: 10-9-20; it is meant to return the existing members email as the new member is in the existing member's categories of interest; but i get blanks

Answer by Clockwork-Muse for Conditional Select in MySQL

As @Shiplu pointed out, this is largely a normalization issue. Despite what some people seem to think, multi-value columns are murder to try to get right.

Your basic issue is:
You have members, who are interested in one or more companies/industries, which belong to one or more industries. You table structure should probably start as:

Industry  ===============  id  -- autoincrement  name  -- varchar    Company  ==============  id  -- autoincrement  name  -- varchar    Company_Industry  ===============  companyId  -- fk reference to Company.id  industryId  -- fk reference to Industry.id    Member  ===============  id  -- autoincrement  name  -- varchar  email  -- varchar    Member_Interest_Industry  =========================  memberId  -- fk reference to Member.id  industryId  -- fk reference to Industry.id    Member_Interest_Company  ========================  memberId  -- fk reference to Member.id  companyId  -- fk reference to Company.id  

To get all companies a member is interested in (directly, or through an industry), you can then run something like this:

SELECT a.name, a.email, c.name  FROM Member as a  JOIN Member_Interest_Company as b  ON b.memberId = a.id  JOIN Company as c  ON c.id = b.companyId  WHERE a.id = :inputParm  UNION   SELECT a.name, a.email, d.name  FROM Member as a  JOIN Member_Interest_Industry as b  ON b.memberId = a.id  JOIN Company_Industry as c  ON c.industryId = b.industryId  JOIN Company as d  ON d.id = c.companyId  WHERE a.id = :inputParm  

Answer by Matt Alcock for Conditional Select in MySQL

Use join SQL syntax rather than a select in style.. You need to join the members table to itself.

Currently:

select id, email   from members where notify_industry in     (select company_industry             from members              where datediff($today, date_activated) <= 1    )  

Use this style:

select m1.id, m1.email   from members m1   inner join members m2 on m1.company_industry = m.notify_industry  where datediff($today, m2.date_activated) <= 1  

Note the use of aliasing to m1 and m2 to help understand which id and emails are returned.

Answer by pilcrow for Conditional Select in MySQL

You should redesign the tables, as others have suggested.

However, barring that, there is a gross hack you can do:

SET sql_mode = 'ANSI';        SELECT notify_members.id, notify_members.email        FROM members notify_members  INNER JOIN members new_members       WHERE CURRENT_DATE - new_members.date_activated <= 1             AND             new_members.company_industry RLIKE ('[[:<:]](' || REPLACE(notify_members.notify_industry, '-', '|') || ')[[:>:]]');  

Yuck. Basically, you turn 9-13 into the MySQL regular expression [[:<:]](9|13)[[:>:]], which matches 9, 13, 13-27-61, etc., but does not match 19-131 and the like. (This supports a compound COMPANY_INDUSTRY field, too.)

Answer by RolandoMySQLDBA for Conditional Select in MySQL

This may get a little ugly but you could try the following

WARNING This will make a Cartesian Product worthy of any Mad Scientist

SELECT NotifyIndustry.id,NotifyIndustry.email  FROM  (      SELECT CONCAT('-',COMPANY_INDUSTRY,'-') company FROM members      WHERE datediff($today, date_activated) <= 1)"  ) CompanyIndustry  INNER JOIN  (      SELECT CONCAT('-', NOTIFY_INDUSTRY,'-') who_to_notify      FROM members  ) NotifyIndustry  ON LOCATE(company,who_to_notify)>0;  

Answer by Aprillion for Conditional Select in MySQL

probably not the fastest query ever but this should do the job:

select m_to_notify.id, m_to_notify.email  from members m_to_notify  join members m_new_member       on '-' || m_to_notify.notify_industry || '-'       like '%-' || m_new_member.company_industry || '-%'  where datediff($today, m_new_memberdate_activated) <= 1)  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72