CODEWITHSUNDEEP
phpmysql
Steven
Steven

Reputation: 61

I am trying to get data from row to match into defined columns

Using PHP / MySQL

I have two tables one is customers the other is cust_data

there is a field that matches the two it is ListID

Now inside the cust_data table the data is listed as cust_data

columns:

ListID  DataName  DataValue
 1      type      something
 1      Region    something

customers columns:

ListID Name Address City State Zip Phone

I want to get the results as

Name Address City State Zip Phone Type Region

Upvotes: 2

Views: 82

Answers (2)

McGarnagle
McGarnagle

Reputation: 102783

You can do it using a group query. The fact that you can group by ListID to get a single row makes this relatively straightforward-- just select conditionally and alias the DataValue field as needed:

SELECT c.Name, c.Address, c.City, c.State, c.Zip, c.Phone,
    MAX (CASE WHEN d.DataName = 'type' THEN DataValue ELSE NULL END) AS  `Type`,
    MAX (CASE WHEN d.DataName = 'Region' THEN DataValue ELSE NULL END) AS  `Region`
FROM customers c, cust_data d
WHERE c.ListID = d.ListID
GROUP BY c.ListID, c.Name, c.Address, c.City, c.State, c.Zip, c.Phone

Upvotes: 0

eggyal
eggyal

Reputation: 125925

SELECT customers.*, data_type.DataValue AS Type, data_region.DataValue AS Region
FROM   customers
  JOIN cust_data AS data_type ON (
         data_type.ListID   = customers.List_ID
     AND data_type.DataName = 'Type'
  )
  JOIN cust_data AS data_region ON (
         data_region.ListID   = customers.List_ID
     AND data_region.DataName = 'Region'
  )

Upvotes: 1

Related Questions