1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

use id from one table and date from another

Discussion in 'MySQL' started by xbat, Sep 13, 2017.

  1. #1
    One table has dates -

    04-01-2017
    04-02-2017
    04-03-2017

    then the next table has names

    john
    ben
    matt
    billy
    bob
    susan

    What I want to try to query out -
    date - p1 p2
    04-01-2017 john ben
    04-01-2017 billy matt
    04-01-2017 susan bob
    04-02-2017 john matt
    04-02-2017 billy bob
    04-02-2017 susan matt
    04-03-17 etc... users can not repeat each other
    SEMrush
     
    xbat, Sep 13, 2017 IP
    SEMrush
  2. xbat

    xbat Active Member

    Messages:
    285
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    53
    #2
    i have tried left join where is null and not null but still i am doing something wrong.


    -- MySQL dump 10.13  Distrib 5.7.17
    --
    -- Host: localhost    Database: schedule
    -- ------------------------------------------------------
    -- Server version    5.7.19
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    --
    -- Table structure for table `name`
    --
    
    DROP TABLE IF EXISTS `name`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `name` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `names` varchar(45) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `name`
    --
    
    LOCK TABLES `name` WRITE;
    /*!40000 ALTER TABLE `name` DISABLE KEYS */;
    INSERT INTO `name` VALUES (1,'john'),(2,'ben'),(3,'matt'),(4,'billy'),(5,'bob'),(6,'susan');
    /*!40000 ALTER TABLE `name` ENABLE KEYS */;
    UNLOCK TABLES;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    -- Dump completed on 2017-09-13 16:57:42
    
    
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    --
    -- Table structure for table `schedule_dates`
    --
    
    DROP TABLE IF EXISTS `schedule_dates`;
    /*!40101 SET @saved_cs_client  = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `schedule_dates` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `date` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `schedule_dates`
    --
    
    LOCK TABLES `schedule_dates` WRITE;
    /*!40000 ALTER TABLE `schedule_dates` DISABLE KEYS */;
    INSERT INTO `schedule_dates` VALUES (1,'9/23/2017'),(2,'9/30/2017'),(3,'10/7/2017'),(4,'10/14/2017'),(5,'10/21/2017'),(6,'11/4/2017'),(7,'11/11/2017'),(8,'11/18/2017'),(9,'11/25/2017'),(10,'12/2/2017'),(11,'12/9/2017'),(12,'12/16/2017'),(13,'12/23/2017'),(14,'12/30/2017'),(15,'1/6/2018'),(16,'1/13/2018'),(17,'1/20/2018'),(18,'1/27/2018'),(19,'2/3/2018'),(20,'2/10/2018'),(21,'2/17/2018'),(22,'2/24/2018'),(23,'3/3/2018'),(24,'3/10/2018');
    /*!40000 ALTER TABLE `schedule_dates` ENABLE KEYS */;
    UNLOCK TABLES;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    
    Code (SQL):
     
    Last edited: Sep 13, 2017
    xbat, Sep 13, 2017 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    23,496
    Likes Received:
    3,176
    Best Answers:
    87
    Trophy Points:
    615
    #3
    Is this what you're after?

    http://sqlfiddle.com/#!9/c6d881/2

    select schedule_dates.date, fn1.names, fn2.names, concat(fn1.names,' ',fn2.names) as name
    from schedule_dates
    , firstnames as fn1 
    , firstnames as fn2
    where fn1.names <> fn2.names
    order by schedule_dates.date, fn1.names
    Code (markup):
     
    sarahk, Sep 13, 2017 IP
  4. xbat

    xbat Active Member

    Messages:
    285
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    53
    #4
    Almost the people can not repeat at on the same day. So in name1 or name2 for that day they can only show once in there.
     
    xbat, Sep 14, 2017 IP