MySQL Forums
Forum List  »  General

CSV Column Filtering - FIND_IN_SET or ?
Posted by: Craig paterson
Date: December 14, 2015 04:42PM

I have Two tables

Companies
ID
Name
Address
City
LeftDate
etc..
People
ID
CID ( Companies ID field stored as CSV)
FName
LName
Left Company (CSV Field)


Each People row can be associated with multiple companies that they are currently or previously worked for. There could be 1 Person that has 3 ID's listed in their CID field like "31,57,235" but their Left company listed as ",09/06/2013,11/26/2013" which means they are only currently working for CID (Companies ID) 31.

I realize this isn't the best system but it is what I am stuck working with. The below will list everyone associated with each company but I would like it to exclude people that have left companies. So in this case the person would only be listed once instead of 3 times.

Query:
SELECT Companies.ClientName,Companies.City,People.FName,People.LName from Companies left join People ON (Companies.ID IN (People.CID)) where Companies.ClientName is not null and (Companies.LeftDate is null or Companies.LeftDate = 0000-00-00) order by Companies.ClientName



I'm doing this with PHP and MySQL but I figured it's just the MySQL I'm having trouble with so this was the best category for it. Any help would be greatly appreciated.

Options: ReplyQuote


Subject
Written By
Posted
CSV Column Filtering - FIND_IN_SET or ?
December 14, 2015 04:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.