Page 1 of 1
how to perform such in single query??
Posted: Mon Oct 26, 2009 11:31 pm
by PHPycho
Hello forums!!
How to query in following case:
Suppose we have two tables
Code: Select all
--------------
table1
--------------
id
title
field
--------------
--------------
table2
--------------
id
title
table1_id (FK to table1)
field (Same field as in table1)
--------------
Question
How to Select all the rows from table2 based on the following:
If table1's 'field' has some value i.e. > 0 then
relate with table2's 'field'
else
relate table1's id & table2's table1_id, and additionally filter by table1.table1_id = some value.
How to perform such in single query?
Thanks in advance for the help.
Re: how to perform such in single query??
Posted: Tue Oct 27, 2009 12:28 am
by PHPycho
Can this be done this way?
MySQL Code:
SELECT ...
FROM table1
INNER JOIN table2
ON
IF(table1.FIELD > 0, table1.FIELD = table2.FIELD, table1.id = table2.table1_id )
Re: how to perform such in single query??
Posted: Tue Oct 27, 2009 3:13 am
by PHPycho
What about this?
SELECT ...
FROM table1
INNER JOIN table2
ON
(table1.field IS NOT NULL AND table1.field = table2.field)
OR
(table1.field IS NULL AND table1.id = table2.table1_id AND table1.table1_id = ?)
Any other alternatives ??
Re: how to perform such in single query??
Posted: Tue Oct 27, 2009 6:20 am
by PHPycho
Knock Knock.. anybody there..
Re: how to perform such in single query??
Posted: Tue Oct 27, 2009 6:21 am
by Weiry
I don't think its possible to do that much with just a single SQL query. It's usually a single statement operation.
My SQL is kinda rusty, but if i were to have a guess, the query could look something like this.
Mind you im really not sure if that will work as is though.
Code: Select all
SELECT a.*,b.*
FROM `table1` a, `table2` b
WHERE (a.`field` > 0 OR a.`id` = b.`table1_id`) AND a.`id` = '<some value>';
PHPycho wrote:then relate with table2's 'field'
I dont know if that is possible, but because you have already selected everything in both tables, why not just select table2's field?
Good luck!

Re: how to perform such in single query??
Posted: Wed Oct 28, 2009 5:15 am
by PHPycho
My real tables are:
shops
---------------------------
id | admin_id | shop_title
---------------------------
1 | 10 | shop1 |
2 | 0 | shop2 |
products
-----------------------------------
id | shop_id | admin_id | title
-----------------------------------
1 | 1 | 0 | product1
2 | 1 | 10 | product2
3 | 2 | 10 | product3
4 | 2 | 0 | product4
Case:
Select all the products from 'products' on the following basis:
if 'shops' has some value for `admin_id` ie `admin_id` > 0 then select based on this field
else
select based on the `shop_id` field
In this case following will be the results:
2 | 1 | 10 | product2
3 | 2 | 10 | product3
1 | 2 | 0 | product1
How to perform in the single query?
Thanks in advance.