1 Aug
2014
1 Aug
'14
4:32 p.m.
Hi all,
I was looking into RFE #1491
<http://sourceforge.net/p/phpmyadmin/feature-requests/1491/> (Support
InnoDB for database Query by example). If I am not wrong, is the concept of
displaying the 'LEFT JOIN' is something like as discussed in the case below:
Lets say I have two tables:
CREATE TABLE `product` (
`id` int(11) NOT NULL,
`price` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `product_order` (
`product_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
KEY `product_category` (`product_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `product_order_ibfk_4` FOREIGN KEY (`product_id`) REFERENCES
`product` (`id`)
)
Now in QBE interface, when I will select `product_order`.`product_id` in
first column drop-down and `product`.`id` in second column drop-down, I
should get the following result:
FROM `db_name`.`product_order` LEFT JOIN `db_name`.`product` ON
`product_order`.`product_id`
= `product`.`id`
In general, 'LEFT JOIN' with foreign table and ON condition between master
column and foreign column. Am I getting it right?
--
Ashutosh Dhundhara
1 Aug
1 Aug
5:15 p.m.
Le 2014-08-01 16:32, Ashutosh Dhundhara a écrit :
Hi all,
I was looking into RFE #1491
<http://sourceforge.net/p/phpmyadmin/feature-requests/1491/> (Support
InnoDB for database Query by example). If I am not wrong, is the concept
of displaying the 'LEFT JOIN' is something like as discussed in the case
below:
Lets say I have two tables:
CREATE TABLE `product` (
`id` int(11) NOT NULL,
`price` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `product_order` (
`product_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
KEY `product_category` (`product_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `product_order_ibfk_4` FOREIGN KEY (`product_id`) REFERENCES
`product` (`id`)
)
Now in QBE interface, when I will select `product_order`.`product_id` in
first column drop-down and `product`.`id` in second column drop-down, I
should get the following result:
FROM `db_name`.`product_order` LEFT JOIN `db_name`.`product`
ON `product_order`.`product_id` = `product`.`id`
In general, 'LEFT JOIN' with foreign table and ON condition between
master column and foreign column. Am I getting it right?
Hi Ashutosh,
For the record, you are using the example given in [0].
Note that to make a more complete example, you have to click
the Show checkboxes for each selected column. Also, you would have to
choose another column, for example the price. When the feature is
implemented, as per your suggestion this would generate:
select `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product_order` LEFT JOIN `product` ON `product_order`.`product_id`
= `product`.`id`
Now, choosing which table is on which side of the LEFT JOIN is open to
interpretation.
The current QBE interface does not permit to specify which table
you want on the left side and it's a shortcoming. It's not clear if
the user wants to show all of the products, even those for which
there are no order. If this is the case, it should become
FROM `product` LEFT JOIN `product_order` ...
I tend to think that this is what the user wants. So, this is the
statement we should aim for:
select `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product` LEFT JOIN `product_order` ON `product_order`.`product_id`
= `product`.`id`
producing results such as:
price product_id id
100 10 10
2000 20 20
3000 NULL 30
where there is no order for product id 30.
I'm not sure whether we should let the user decide which table should be
on the left side. Things can become tricky in the UI when you add a
third table like customer.
[0] http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
--
Marc Delisle | phpMyAdmin
2 Aug
2 Aug
6:42 a.m.
On Sat, Aug 2, 2014 at 2:45 AM, Marc Delisle <marc(a)infomarc.info> wrote:
Le 2014-08-01 16:32, Ashutosh Dhundhara a écrit :
Thanks Marc,
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:
SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product` LEFT JOIN `product_order` ON `product_order`.`product_id`
= `product`.`id`
But if we talk about the current scenario, in case of 'Internal relations',
the QBE generates the following statement:
SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product_order` LEFT JOIN `product` ON `product_order`.`product_id`
= `product`.`id`
I selected `product`.`price`, `product_order`.`product_id` and
`product`.`id` in columns 1, 2 and 3 respectively with 'Show' checkboxes
checked.
And I have an internal relation defined from `product_order`.`product_id`
to `product`.`id`.
I am a little confused here. Am I doing something wrong?
--
Ashutosh Dhundhara
Hi all,
I was looking into RFE #1491
<http://sourceforge.net/p/phpmyadmin/feature-requests/1491/> (Support
InnoDB for database Query by example). If I am not wrong, is the concept
of displaying the 'LEFT JOIN' is something like as discussed in the case
below:
Lets say I have two tables:
CREATE TABLE `product` (
`id` int(11) NOT NULL,
`price` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `product_order` (
`product_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
KEY `product_category` (`product_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `product_order_ibfk_4` FOREIGN KEY (`product_id`) REFERENCES
`product` (`id`)
)
Now in QBE interface, when I will select `product_order`.`product_id` in
first column drop-down and `product`.`id` in second column drop-down, I
should get the following result:
FROM `db_name`.`product_order` LEFT JOIN `db_name`.`product`
ON `product_order`.`product_id` = `product`.`id`
In general, 'LEFT JOIN' with foreign table and ON condition between
master column and foreign column. Am I getting it right?
Hi Ashutosh,
For the record, you are using the example given in [0].
Note that to make a more complete example, you have to click
the Show checkboxes for each selected column. Also, you would have to
choose another column, for example the price. When the feature is
implemented, as per your suggestion this would generate:
select `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product_order` LEFT JOIN `product` ON `product_order`.`product_id`
= `product`.`id`
Now, choosing which table is on which side of the LEFT JOIN is open to
interpretation.
The current QBE interface does not permit to specify which table
you want on the left side and it's a shortcoming. It's not clear if
the user wants to show all of the products, even those for which
there are no order. If this is the case, it should become
FROM `product` LEFT JOIN `product_order` ...
I tend to think that this is what the user wants. So, this is the
statement we should aim for:
select `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product` LEFT JOIN `product_order` ON `product_order`.`product_id`
= `product`.`id`
producing results such as:
price product_id id
100 10 10
2000 20 20
3000 NULL 30
where there is no order for product id 30.
I'm not sure whether we should let the user decide which table should be
on the left side. Things can become tricky in the UI when you add a
third table like customer.
[0] http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
7:58 a.m.
Le 2014-08-02 06:42, Ashutosh Dhundhara a écrit :
On Sat, Aug 2, 2014 at 2:45 AM, Marc Delisle
<marc(a)infomarc.info
<mailto:marc@infomarc.info>> wrote:
Le 2014-08-01 16:32, Ashutosh Dhundhara a écrit :
This is the current behavior of QBE for internal relations. In
libraries/DBQbe.class.php, if you look for "LEFT JOIN", you'll find what
is the logic used.
So much the better if this logic can be improved when doing your task
with InnoDB. If not, we can leave the logic for internal relations as it
is currently.
--
Marc Delisle | phpMyAdmin
Hi all,
I was looking into RFE #1491
<http://sourceforge.net/p/phpmyadmin/feature-requests/1491/> (Support
InnoDB for database Query by example). If I am not wrong, is the
concept
of displaying the 'LEFT JOIN' is
something like as discussed in
the case
below:
Lets say I have two tables:
CREATE TABLE `product` (
`id` int(11) NOT NULL,
`price` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `product_order` (
`product_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
KEY `product_category` (`product_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `product_order_ibfk_4` FOREIGN KEY (`product_id`)
REFERENCES
`product` (`id`)
)
Now in QBE interface, when I will select
`product_order`.`product_id` in
first column drop-down and `product`.`id` in
second column
drop-down, I
should get the following result:
FROM `db_name`.`product_order` LEFT JOIN `db_name`.`product`
ON `product_order`.`product_id` = `product`.`id`
In general, 'LEFT JOIN' with foreign table and ON condition between
master column and foreign column. Am I getting it right?
Hi Ashutosh,
For the record, you are using the example given in [0].
Note that to make a more complete example, you have to click
the Show checkboxes for each selected column. Also, you would have to
choose another column, for example the price. When the feature is
implemented, as per your suggestion this would generate:
select `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product_order` LEFT JOIN `product` ON `product_order`.`product_id`
= `product`.`id`
Now, choosing which table is on which side of the LEFT JOIN is open to
interpretation.
The current QBE interface does not permit to specify which table
you want on the left side and it's a shortcoming. It's not clear if
the user wants to show all of the products, even those for which
there are no order. If this is the case, it should become
FROM `product` LEFT JOIN `product_order` ...
I tend to think that this is what the user wants. So, this is the
statement we should aim for:
select `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product` LEFT JOIN `product_order` ON `product_order`.`product_id`
= `product`.`id`
producing results such as:
price product_id id
100 10 10
2000 20 20
3000 NULL 30
where there is no order for product id 30.
I'm not sure whether we should let the user decide which table should be
on the left side. Things can become tricky in the UI when you add a
third table like customer.
[0]
http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
Thanks Marc,
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:
SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product` LEFT JOIN `product_order` ON `product_order`.`product_id`
= `product`.`id`
But if we talk about the current scenario, in case of 'Internal
relations', the QBE generates the following statement:
SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product_order` LEFT JOIN `product` ON `product_order`.`product_id`
= `product`.`id`
I selected `product`.`price`, `product_order`.`product_id` and
`product`.`id` in columns 1, 2 and 3 respectively with 'Show' checkboxes
checked.
And I have an internal relation defined from
`product_order`.`product_id` to `product`.`id`.
I am a little confused here. Am I doing something wrong?
3 Aug
3 Aug
1:37 p.m.
On Sat, Aug 2, 2014 at 5:28 PM, Marc Delisle <marc(a)infomarc.info> wrote:
Le 2014-08-02 06:42, Ashutosh Dhundhara a écrit :
`product`.`id`
Thanks Marc.
I tried to improve the current logic for internal relations as suggested by
you. It works fine for my test cases.
Can you point me to some more complex test cases for this or should I
create a pull request?
--
Ashutosh Dhundhara
On Sat, Aug 2, 2014 at 2:45 AM, Marc Delisle
<marc(a)infomarc.info
<mailto:marc@infomarc.info>> wrote:
Le 2014-08-01 16:32, Ashutosh Dhundhara a écrit :
> Hi all,
>
> I was looking into RFE #1491
> <http://sourceforge.net/p/phpmyadmin/feature-requests/1491/>
(Support
InnoDB
for database Query by example). If I am not wrong, is the
concept
of displaying the 'LEFT JOIN' is
something like as discussed in
the case
below:
Lets say I have two tables:
CREATE TABLE `product` (
`id` int(11) NOT NULL,
`price` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `product_order` (
`product_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
KEY `product_category` (`product_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `product_order_ibfk_4` FOREIGN KEY (`product_id`)
REFERENCES
`product` (`id`)
)
Now in QBE interface, when I will select
`product_order`.`product_id` in
first column drop-down and `product`.`id` in
second column
drop-down, I
should get the following result:
FROM `db_name`.`product_order` LEFT JOIN `db_name`.`product`
ON `product_order`.`product_id` = `product`.`id`
In general, 'LEFT JOIN' with foreign table and ON condition between
master column and foreign column. Am I getting it right?
Hi Ashutosh,
For the record, you are using the example given in [0].
Note that to make a more complete example, you have to click
the Show checkboxes for each selected column. Also, you would have to
choose another column, for example the price. When the feature is
implemented, as per your suggestion this would generate:
select `product`.`price`, `product_order`.`product_id`, FROM `product_order` LEFT JOIN `product` ON
`product_order`.`product_id`
= `product`.`id`
Now, choosing which table is on which side of the LEFT JOIN is open
to
interpretation.
The current QBE interface does not permit to specify which table
you want on the left side and it's a shortcoming. It's not clear if
the user wants to show all of the products, even those for which
there are no order. If this is the case, it should become
FROM `product` LEFT JOIN `product_order` ...
I tend to think that this is what the user wants. So, this is the
statement we should aim for:
select `product`.`price`, `product_order`.`product_id`,
`product`.`id`
FROM `product` LEFT JOIN `product_order` ON
`product_order`.`product_id`
= `product`.`id`
producing results such as:
price product_id id
100 10 10
2000 20 20
3000 NULL 30
where there is no order for product id 30.
I'm not sure whether we should let the user decide which table
should be
on the left side. Things can become tricky in
the UI when you add a
third table like customer.
[0]
http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
Thanks Marc,
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:
SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product` LEFT JOIN `product_order` ON `product_order`.`product_id`
= `product`.`id`
But if we talk about the current scenario, in case of 'Internal
relations', the QBE generates the following statement:
SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product_order` LEFT JOIN `product` ON `product_order`.`product_id`
= `product`.`id`
I selected `product`.`price`, `product_order`.`product_id` and
`product`.`id` in columns 1, 2 and 3 respectively with 'Show' checkboxes
checked.
And I have an internal relation defined from
`product_order`.`product_id` to `product`.`id`.
I am a little confused here. Am I doing something wrong?
This is the current behavior of QBE for internal relations. In
libraries/DBQbe.class.php, if you look for "LEFT JOIN", you'll find what
is the logic used.
So much the better if this logic can be improved when doing your task
with InnoDB. If not, we can leave the logic for internal relations as it
is currently.
5:07 p.m.
Le 2014-08-03 13:37, Ashutosh Dhundhara a écrit :
On Sat, Aug 2, 2014 at 5:28 PM, Marc Delisle
<marc(a)infomarc.info
<mailto:marc@infomarc.info>> wrote:
Le 2014-08-02 06:42, Ashutosh Dhundhara a écrit :
between
have to
Please create a pull request.
--
Marc Delisle | phpMyAdmin
On Sat, Aug 2, 2014 at 2:45 AM, Marc Delisle
<marc(a)infomarc.info
<mailto:marc@infomarc.info>
<mailto:marc@infomarc.info
<mailto:marc@infomarc.info>>> wrote:
Le 2014-08-01 16:32, Ashutosh Dhundhara a écrit :
> Hi all,
>
> I was looking into RFE #1491
> <http://sourceforge.net/p/phpmyadmin/feature-requests/1491/>
(Support
InnoDB
for database Query by example). If I am not wrong, is the
concept
of displaying the 'LEFT JOIN' is
something like as discussed in
the case
below:
Lets say I have two tables:
CREATE TABLE `product` (
`id` int(11) NOT NULL,
`price` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `product_order` (
`product_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
KEY `product_category` (`product_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `product_order_ibfk_4` FOREIGN KEY (`product_id`)
REFERENCES
`product` (`id`)
)
Now in QBE interface, when I will select
`product_order`.`product_id` in
first column drop-down and `product`.`id` in
second column
drop-down, I
> should get the following result:
>
> FROM `db_name`.`product_order` LEFT JOIN `db_name`.`product`
> ON `product_order`.`product_id` = `product`.`id`
>
> In general, 'LEFT JOIN' with foreign table and ON condition master
column and foreign column. Am I getting it right?
Hi Ashutosh,
For the record, you are using the example given in [0].
Note that to make a more complete example, you have to click
the Show checkboxes for each selected column. Also, you would choose another column, for example the price.
When the feature is
implemented, as per your suggestion this would generate:
select `product`.`price`, `product_order`.`product_id`,
`product`.`id`
FROM `product_order` LEFT JOIN `product` ON
`product_order`.`product_id`
= `product`.`id`
Now, choosing which table is on which side of the LEFT JOIN is
open to
interpretation.
The current QBE interface does not permit to specify which table
you want on the left side and it's a shortcoming. It's not
clear
if
the user wants to show all of the products,
even those for which
there are no order. If this is the case, it should become
FROM `product` LEFT JOIN `product_order` ...
I tend to think that this is what the user wants. So, this is the
statement we should aim for:
select `product`.`price`, `product_order`.`product_id`,
`product`.`id`
FROM `product` LEFT JOIN `product_order` ON
`product_order`.`product_id`
= `product`.`id`
producing results such as:
price product_id id
100 10 10
2000 20 20
3000 NULL 30
where there is no order for product id 30.
I'm not sure whether we should let the user decide which table
should
be
on the left side. Things can become tricky in
the UI when you
add a
third table like customer.
[0]
http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
Thanks Marc,
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:
SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product` LEFT JOIN `product_order` ON
`product_order`.`product_id`
= `product`.`id`
But if we talk about the current scenario, in case of 'Internal
relations', the QBE generates the following statement:
SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product_order` LEFT JOIN `product` ON
`product_order`.`product_id`
= `product`.`id`
I selected `product`.`price`, `product_order`.`product_id` and
`product`.`id` in columns 1, 2 and 3 respectively with 'Show'
checkboxes
checked.
And I have an internal relation defined from
`product_order`.`product_id` to `product`.`id`.
I am a little confused here. Am I doing something wrong?
This is the current behavior of QBE for internal relations. In
libraries/DBQbe.class.php, if you look for "LEFT JOIN", you'll find
what
is the logic used.
So much the better if this logic can be improved when doing your task
with InnoDB. If not, we can leave the logic for internal relations as it
is currently.
Thanks Marc.
I tried to improve the current logic for internal relations as suggested
by you. It works fine for my test cases.
Can you point me to some more complex test cases for this or should I
create a pull request?
7:06 p.m.
On Sat, Aug 2, 2014 at 2:45 AM, Marc Delisle <marc(a)infomarc.info> wrote:
Le 2014-08-01 16:32, Ashutosh Dhundhara a écrit :
Hi Marc,
In case of the complex example given in [0], if we select `product`.`id`,
`product`.`price` and `product_order`.`product_id` columns then should the
QBE interface generate the following SQL:
SELECT `product`.`id`, `product`.`price`, `product_order`.`product_id`
FROM `product`
LEFT JOIN `GSoC14`.`product_order` ON `product`.`category` =
`product_order`.`product_category` AND `product`.`id` =
`product_order`.`product_id`
Also, if we select `product`.`id`, `customer`.`id` and
`product_order`.`product_id` then what should be the output in case of 3
tables?
[0] http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
--
Ashutosh Dhundhara
Hi all,
I was looking into RFE #1491
<http://sourceforge.net/p/phpmyadmin/feature-requests/1491/> (Support
InnoDB for database Query by example). If I am not wrong, is the concept
of displaying the 'LEFT JOIN' is something like as discussed in the case
below:
Lets say I have two tables:
CREATE TABLE `product` (
`id` int(11) NOT NULL,
`price` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `product_order` (
`product_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
KEY `product_category` (`product_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `product_order_ibfk_4` FOREIGN KEY (`product_id`) REFERENCES
`product` (`id`)
)
Now in QBE interface, when I will select `product_order`.`product_id` in
first column drop-down and `product`.`id` in second column drop-down, I
should get the following result:
FROM `db_name`.`product_order` LEFT JOIN `db_name`.`product`
ON `product_order`.`product_id` = `product`.`id`
In general, 'LEFT JOIN' with foreign table and ON condition between
master column and foreign column. Am I getting it right?
Hi Ashutosh,
For the record, you are using the example given in [0].
Note that to make a more complete example, you have to click
the Show checkboxes for each selected column. Also, you would have to
choose another column, for example the price. When the feature is
implemented, as per your suggestion this would generate:
select `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product_order` LEFT JOIN `product` ON `product_order`.`product_id`
= `product`.`id`
Now, choosing which table is on which side of the LEFT JOIN is open to
interpretation.
The current QBE interface does not permit to specify which table
you want on the left side and it's a shortcoming. It's not clear if
the user wants to show all of the products, even those for which
there are no order. If this is the case, it should become
FROM `product` LEFT JOIN `product_order` ...
I tend to think that this is what the user wants. So, this is the
statement we should aim for:
select `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product` LEFT JOIN `product_order` ON `product_order`.`product_id`
= `product`.`id`
producing results such as:
price product_id id
100 10 10
2000 20 20
3000 NULL 30
where there is no order for product id 30.
I'm not sure whether we should let the user decide which table should be
on the left side. Things can become tricky in the UI when you add a
third table like customer.
[0] http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
4 Aug
4 Aug
7:04 a.m.
Ashutosh Dhundhara a écrit :
On Sat, Aug 2, 2014 at 2:45 AM, Marc Delisle
<marc(a)infomarc.info> wrote:
Yes, this looks fine; to prove it, enter some data in these tables and
try it.
Le 2014-08-01 16:32, Ashutosh Dhundhara a écrit
:
Hi Marc,
In case of the complex example given in [0], if we select `product`.`id`,
`product`.`price` and `product_order`.`product_id` columns then should the
QBE interface generate the following SQL:
SELECT `product`.`id`, `product`.`price`, `product_order`.`product_id`
FROM `product`
LEFT JOIN `GSoC14`.`product_order` ON `product`.`category` =
`product_order`.`product_category` AND `product`.`id` =
`product_order`.`product_id` Hi all,
I was looking into RFE #1491
<http://sourceforge.net/p/phpmyadmin/feature-requests/1491/> (Support
InnoDB for database Query by example). If I am not wrong, is the concept
of displaying the 'LEFT JOIN' is something like as discussed in the case
below:
Lets say I have two tables:
CREATE TABLE `product` (
`id` int(11) NOT NULL,
`price` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `product_order` (
`product_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
KEY `product_category` (`product_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `product_order_ibfk_4` FOREIGN KEY (`product_id`) REFERENCES
`product` (`id`)
)
Now in QBE interface, when I will select `product_order`.`product_id` in
first column drop-down and `product`.`id` in second column drop-down, I
should get the following result:
FROM `db_name`.`product_order` LEFT JOIN `db_name`.`product`
ON `product_order`.`product_id` = `product`.`id`
In general, 'LEFT JOIN' with foreign table and ON condition between
master column and foreign column. Am I getting it right?
Hi Ashutosh,
For the record, you are using the example given in [0].
Note that to make a more complete example, you have to click
the Show checkboxes for each selected column. Also, you would have to
choose another column, for example the price. When the feature is
implemented, as per your suggestion this would generate:
select `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product_order` LEFT JOIN `product` ON `product_order`.`product_id`
= `product`.`id`
Now, choosing which table is on which side of the LEFT JOIN is open to
interpretation.
The current QBE interface does not permit to specify which table
you want on the left side and it's a shortcoming. It's not clear if
the user wants to show all of the products, even those for which
there are no order. If this is the case, it should become
FROM `product` LEFT JOIN `product_order` ...
I tend to think that this is what the user wants. So, this is the
statement we should aim for:
select `product`.`price`, `product_order`.`product_id`, `product`.`id`
FROM `product` LEFT JOIN `product_order` ON `product_order`.`product_id`
= `product`.`id`
producing results such as:
price product_id id
100 10 10
2000 20 20
3000 NULL 30
where there is no order for product id 30.
I'm not sure whether we should let the user decide which table should be
on the left side. Things can become tricky in the UI when you add a
third table like customer.
[0] http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
Also, if we select `product`.`id`, `customer`.`id` and
`product_order`.`product_id` then what should be the output in case of 3
tables?
I am not sure how the UI can help the user indicate his/her intentions
in the case of three tables. Have a look at [1] for an example of double
LEFT JOIN.
[1] http://stackoverflow.com/questions/8416967/double-left-join
--
Marc Delisle (phpMyAdmin)
3694
days inactive
3697
days old
7 comments
2 participants
participants (2)
-
Ashutosh Dhundhara -
Marc Delisle