<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Sat, Aug 2, 2014 at 2:45 AM, Marc Delisle <span dir="ltr"><<a href="mailto:marc@infomarc.info" target="_blank">marc@infomarc.info</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">Le 2014-08-01 16:32, Ashutosh Dhundhara a écrit :<br>
<div class="">> Hi all,<br>
><br>
> I was looking into RFE #1491<br>
</div>> <<a href="http://sourceforge.net/p/phpmyadmin/feature-requests/1491/" target="_blank">http://sourceforge.net/p/phpmyadmin/feature-requests/1491/</a>> (Support<br>
<div><div class="h5">> InnoDB for database Query by example). If I am not wrong, is the concept<br>
> of displaying the 'LEFT JOIN' is something like as discussed in the case<br>
> below:<br>
><br>
> Lets say I have two tables:<br>
><br>
> CREATE TABLE `product` (<br>
> `id` int(11) NOT NULL,<br>
> `price` decimal(10,0) DEFAULT NULL,<br>
> PRIMARY KEY (`id`)<br>
> )<br>
><br>
> CREATE TABLE `product_order` (<br>
> `product_id` int(11) NOT NULL,<br>
> `customer_id` int(11) NOT NULL,<br>
> KEY `product_category` (`product_id`),<br>
> KEY `customer_id` (`customer_id`),<br>
> CONSTRAINT `product_order_ibfk_4` FOREIGN KEY (`product_id`) REFERENCES<br>
> `product` (`id`)<br>
> )<br>
><br>
> Now in QBE interface, when I will select `product_order`.`product_id` in<br>
> first column drop-down and `product`.`id` in second column drop-down, I<br>
> should get the following result:<br>
><br>
> FROM `db_name`.`product_order` LEFT JOIN `db_name`.`product`<br>
> ON `product_order`.`product_id` = `product`.`id`<br>
><br>
> In general, 'LEFT JOIN' with foreign table and ON condition between<br>
> master column and foreign column. Am I getting it right?<br>
<br>
</div></div>Hi Ashutosh,<br>
<br>
For the record, you are using the example given in [0].<br>
<br>
Note that to make a more complete example, you have to click<br>
the Show checkboxes for each selected column. Also, you would have to<br>
choose another column, for example the price. When the feature is<br>
implemented, as per your suggestion this would generate:<br>
<br>
select `product`.`price`, `product_order`.`product_id`, `product`.`id`<br>
FROM `product_order` LEFT JOIN `product` ON `product_order`.`product_id`<br>
= `product`.`id`<br>
<br>
Now, choosing which table is on which side of the LEFT JOIN is open to<br>
interpretation.<br>
<br>
The current QBE interface does not permit to specify which table<br>
you want on the left side and it's a shortcoming. It's not clear if<br>
the user wants to show all of the products, even those for which<br>
there are no order. If this is the case, it should become<br>
<br>
FROM `product` LEFT JOIN `product_order` ...<br>
<br>
I tend to think that this is what the user wants. So, this is the<br>
statement we should aim for:<br>
<br>
select `product`.`price`, `product_order`.`product_id`, `product`.`id`<br>
FROM `product` LEFT JOIN `product_order` ON `product_order`.`product_id`<br>
= `product`.`id`<br>
<br>
producing results such as:<br>
<br>
price product_id id<br>
100 10 10<br>
2000 20 20<br>
3000 NULL 30<br>
<br>
where there is no order for product id 30.<br>
<br>
I'm not sure whether we should let the user decide which table should be<br>
on the left side. Things can become tricky in the UI when you add a<br>
third table like customer.<br>
<br>
[0] <a href="http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html" target="_blank">http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html</a><br><br></blockquote><div><br></div><div>Thanks Marc,</div>
<div><br></div><div>That was quite comprehensive description of the problem. I also think that user generally want to display (in context with above example) all the products, even those for which there are no order. So in that case following statement becomes our objective:</div>
<div><br></div><div>SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`<br>FROM <font color="#ff0000">`product` LEFT JOIN `product_order`</font> ON `product_order`.`product_id`<br>= `product`.`id`<br></div>
</div><div class="gmail_extra"><br></div>But if we talk about the current scenario, in case of 'Internal relations', the QBE generates the following statement:</div><div class="gmail_extra"><br></div><div class="gmail_extra">
SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`<br>FROM <font color="#ff0000">`product_order` LEFT JOIN `product`</font> ON `product_order`.`product_id`<br>= `product`.`id`<br><br clear="all"><div>I selected `product`.`price`, `product_order`.`product_id` and `product`.`id` in columns 1, 2 and 3 respectively with 'Show' checkboxes checked.</div>
<div>And I have an internal relation defined from <font color="#ff0000">`product_order`.`product_id` to `product`.`id`</font><font color="#000000">.</font></div><div>I am a little confused here. Am I doing something wrong?</div>
<div><br></div><div><br></div>-- <br><div dir="ltr"><div>Ashutosh Dhundhara</div></div>
</div></div>