Merging columns

[lemmin]

Is there a way to merge data over NULL values?

 

I have a result table that has two rows with the same id, but one has a value for a column that the other has a null value for (and the likewise for the other row).

 

Something like this:

ID               Value1            Value2

1                  hi               NULL
1                 NULL               bye

 

Is it possible to merge the similar IDs together instead of just dropping the first one (like GROUP BY does)?

 

Thanks

[Mchl]

How does the query that produces this result look like?

[lemmin]

Ridiculous.

 

SELECT
         ae.FilledPositions,
         ae.MaxPositions,
         ae.EventDate,
         ae.TotalEvents,
         a.Description,
         a.ActivityID,
         a.Title,
         CONCAT("images/", d.Image16) as Dot,
         IF(
            DATEDIFF(IF(ae.EventDate IS NULL, ae.PositionDate, ae.EventDate), NOW())=1,
            "<b>Tomorrow</b>",
            IF(
               DATEDIFF(IF(ae.EventDate IS NULL, ae.PositionDate, ae.EventDate), NOW())=0,
               "<b>Today!</b>",
               DATE_FORMAT(IF(ae.EventDate IS NULL, ae.PositionDate, ae.EventDate), "<b>%b %D</b> at <b>%l:%i %p</b>")
               )
            ) as NextEvent
      FROM
         dots d,
         activities a
         LEFT JOIN (
            SELECT
               COUNT(assoc.PositionID) as FilledPositions,
               (SELECT COALESCE(SUM(MaxPositions),0) FROM activity_event_positions WHERE EventID = e.EventID GROUP BY EventID) MaxPositions,
               aep.PositionDate,
               COALESCE(COUNT(e.EventID),0) TotalEvents,
               e.EventDate,
               e.ActivityID,
               e.EventID
            FROM
               activity_events e
               LEFT JOIN (
                  SELECT
                     p.PositionID,
                     p.PositionDate,
                     p.EventID
                  FROM
                     activity_event_positions p
                  ORDER BY
                     p.PositionDate IS NULL,
                     p.PositionDate
               ) aep ON aep.EventID = e.EventID
               LEFT JOIN activity_event_positions_assoc assoc ON assoc.PositionID = aep.PositionID
            WHERE
               (e.EventDate > NOW() OR
               e.EventDate IS NULL)
            GROUP BY
               e.EventID
            ORDER BY
               e.EventDate > aep.PositionDate,
               e.EventDate IS NULL,
               e.EventDate
         ) ae ON a.ActivityID = ae.ActivityID

 

My goal is to return the next occurring of the two fields, EventDate and PositionDate.

[fenway]

Not a chance that anyone but you can read through that.

[Mchl]

You could try using GROUP_CONCAT() (look it up in MySQL manual)

[lemmin]

Oh come on, fenway. Have more confidence! Just kidding. I didn't post it originally because of that reason, but Mchl asked for it!

 

I supposed I might be able to make GROUP_CONCAT() work, but it would return the values as a string, wouldn't it? I was hoping that, based on the result table I showed in my first post, someone would recognize a way to query for that differently. Maybe I can come up with a simple concept query that would be easier to look at.

 

Thanks for the help.

[Mchl]

I supposed I might be able to make GROUP_CONCAT() work, but it would return the values as a string, wouldn't it?

 

Yes it would, which might or might not be an issue depending on what you're going to do with this dataset. If it's fetched by PHP script, it doesn't really matter if these are strings or not.