1

i have MYSQL database where a table have a field that contains multiple values in each cell. where these values are a foreign key of another table.

inspection_date:

  • siteID (primary key)
  • employeeID(foreign key)
  • inspectionDate

where the emplyee field will have multiple values be cause more than one employee had done the inspection.

employee

  • employeeID(primary key)
  • employeeName

can anyone help me to use select query

mysql :

$query_site_location =$wpdb->get_results("select DISTINCT 
                      l.siteID,
                      l.locationName, 
                      n.inspectionDate,
                      s.employeeID
                       from site_location l
                       LEFT    
                       JOIN inspection_info n
                       on l.siteID = n.siteID
                       LEFT  
                       JOIN employee s
                       on n.employeeID = s.employeeID");

the result must be in the field of employee (6-7) for the first row

Dany 7elo
  • 73
  • 2
  • 10
  • 2
    The standard solution to "deal with" this problem is to design your datatables and relations so that you're not storing multiple values in a single cell.... it's always bad design, and creates many. many problems. normalize your data tables – Mark Baker Nov 06 '17 at 15:53
  • What data you want to get? – purush Nov 06 '17 at 15:55
  • 2
    It sounds like there's a one-to-many relationship between "inspection_info" entity and "employee" entity. Storing multiple values in a column flies in the face of entity-relationship modeling and relational database theory. Don't do it. https://stackoverflow.com/questions/20220390/how-to-model-one-to-many-relationship-in-database – spencer7593 Nov 06 '17 at 16:01
  • guys i have this scenario where 3 employees do an inspection in 5/2/2017 how can i store these data based on the database – Dany 7elo Nov 06 '17 at 16:16
  • so can i use the inspectionDate as a foreign key in the employee entity? – Dany 7elo Nov 06 '17 at 16:18
  • 1. What is the problem? I can see no answerable question here ("How to deal...?" => "Just parse it"). 2. "How can i store [this] data?" => See the Spencer's link. – RandomSeed Nov 07 '17 at 12:50

1 Answers1

0

A THIS relation is many to many we must create a third entity in order to handle the primary keys of both tables.

employee:

employeeID(PRIMARY KEY)
employeeName

inspection_info:

inspectionID(PRIMARY KEY)
inspectionDate
employeeID

inspection_site:

inspectionID(PRIMARY KEY)

siteID(PRIMARY KEY)
Dany 7elo
  • 73
  • 2
  • 10