0

Mysql query to match and join two rows for a given date. Hi I am newbie, have googled and searched stack overflow for a days but I can't find the answer. Please help with the following problem

I have a table: people

Id   Leader Name1   Name2   StartDate  EndDate
123  1      Person1         2013-02-11 2013-02-17
123  0              Person2 2013-02-13 2013-02-13
123  0              Person3 2013-02-13 2013-02-13

I have a query1

(SELECT t1.Id, t1.Name1, t1.Name2
 FROM `dbo`.`people` t1
WHERE t1.StartDate >= '2013-02-11'
AND t1.Leader = 1)

UNION 

(SELECT t2.Id, t2.Name1, t1.Name2
FROM `dbo`.`people` t2
WHERE t2.StartDate >= '2013-02-11'
AND t2.Leader = 0)

This returns

Id   Name1   Name2
123, Person1

I have a query2

(SELECT t1.Id, t1.Name1, t1.Name2
FROM `dbo`.`people` t1
WHERE t1.StartDate >= '2013-02-13'
AND t1.Leader = 1)

UNION 

(SELECT t2.Id, t2.Name1, t1.Name2
FROM `dbo`.`people` t2
WHERE t2.StartDate >= '2013-02-13'
AND t2.Leader = 0)

This returns

Id   Name1   Name2
123, Person1 
123,         Person2
123,         Person3

I need the result for 2012-02-11 to show

Id   Name1   Name2
123, Person1 null

and for the 2012-02-13 to show

Id   Name1   Name2
123, Person1 Person2
123, Person1 Person3
user1254513
  • 71
  • 2
  • 8
  • Does your table definition include NOT NULL for Name2? It might be helpful if you included the results of this query: Explain people – starshine531 Feb 09 '13 at 12:05
  • Field,Type,Null,Key,Default,Extra Counter,int(11),NO,PRI,NULL, id,int(11),YES,,NULL, leader,int(11),YES,,NULL, name1,varchar(45),YES,,NULL, name,varchar(45),YES,,NULL, startdate,varchar(45),YES,,NULL, enddate,varchar(45),YES,,NULL, – user1254513 Feb 09 '13 at 12:15

1 Answers1

0

Why are you doing these unions? You understand that union adds together two result sets without comparing them, yes? Perhaps you're trying to do a self join? If so, you'll find an example here: How does a MYSQL Self-Join Work?

Also, is your table normalized? see http://en.wikipedia.org/wiki/Database_normalization You might have a better time of this if you split the table up. A common beginner error is trying to cram too much information into one table. Why do you have 'name 1' and 'name 2'? Perhaps you should have a separate 'leader' table if the information you store is different for a leader than a non-leader and then relate them with an id. Why in your table is the id column always the same? What is that id for? Are these people part of the same group somehow?

Here's some added information on joins as well that might help you understand:

http://dev.mysql.com/doc/refman/5.0/en/join.html

http://en.wikipedia.org/wiki/Join_%28SQL%29

Community
  • 1
  • 1
starshine531
  • 601
  • 5
  • 19
  • I understand the syntax of join on the same table. what would be the syntax of join on this results of query from: (SELECT t1.Id, t1.Name1, t1.Name2 FROM `dbo`.`people` t1 WHERE t1.StartDate >= '2013-02-13' AND t1.Leader = 1) UNION (SELECT t2.Id, t2.Name1, t1.Name2 FROM `dbo`.`people` t2 WHERE t2.StartDate >= '2013-02-13' AND t2.Leader = 0) – user1254513 Feb 09 '13 at 12:34
  • The syntax would be the same as the self join in my answer. You want your select to have t1.Name1, t2.Name2 and you'd join on id. Again, you should consider redesigning your table. You're going to end up with a lot of messy queries if you don't. – starshine531 Feb 09 '13 at 12:51