Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Query Builder Table Order And Joins

forums forums SQLyog Using SQLyog Query Builder Table Order And Joins

This topic contains 1 reply, has 0 voices, and was last updated by  sudwebdesign 7 years, 1 month ago.

  • Author
    Posts
  • #12745

    sudwebdesign
    Member

    Hello

    I have a simple two table relationship customer->actions.

    I have define a foreign key relationship using the schema designer and the resulting SQL is as follows:

    DROP TABLE IF EXISTS `action`;

    CREATE TABLE `action` (

    `id` int(3) NOT NULL AUTO_INCREMENT,

    `customer_id` int(3) DEFAULT NULL,

    `action` varchar(49) DEFAULT NULL,

    `enquiry_outcome` varchar(70) DEFAULT NULL,

    `signup` varchar(39) DEFAULT NULL,

    `action_date` varchar(43) DEFAULT NULL,

    `product_name` varchar(40) DEFAULT NULL,

    `product_SKU` varchar(33) DEFAULT NULL,

    `item_value` varchar(72) DEFAULT NULL,

    `product_type` varchar(66) DEFAULT NULL,

    `custom_made` varchar(71) DEFAULT NULL,

    `gender` varchar(66) DEFAULT NULL,

    `reason_for_purchase` varchar(229) DEFAULT NULL,

    PRIMARY KEY (`id`),

    KEY `customer_idx` (`customer_id`),

    CONSTRAINT `action_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)

    ) ENGINE=InnoDB AUTO_INCREMENT=288 DEFAULT CHARSET=utf8;

    /*Table structure for table `customer` */

    DROP TABLE IF EXISTS `customer`;

    CREATE TABLE `customer` (

    `id` int(3) NOT NULL,

    `surname` varchar(55) DEFAULT NULL,

    `forename` varchar(55) DEFAULT NULL,

    `email` varchar(255) DEFAULT NULL,

    `comment` varchar(255) DEFAULT NULL,

    `phone` varchar(25) DEFAULT NULL,

    `skype` varchar(55) DEFAULT NULL,

    `address` varchar(255) DEFAULT NULL,

    `city` varchar(25) DEFAULT NULL,

    `postcode` varchar(25) DEFAULT NULL,

    `country` varchar(25) DEFAULT NULL,

    `gender` enum('F','M','') DEFAULT NULL,

    `repeat_purchaser` tinyint(1) DEFAULT NULL,

    `date_created` date DEFAULT NULL,

    PRIMARY KEY (`id`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    When I go in to use the query builder against these two tables it always places the `actions` table as the primary table in the query (see action-join.jpg).

    [attachment=1771:action-join.jpg]

    The foreign key constraint is defined on the `actions` table, so surely this should be the child table.

    To get it to work, I have to delete the relationship and re-create it in the query builder (see customer-join.jpg)

    [attachment=1772:customer-join.jpg]

    Am I doing something wrong in the schema generator?

    Thanks

    Chris

You must be logged in to reply to this topic.