how to perform such in single query?

Forum Moderators: open

Message Too Old, No Replies

how to perform such in single query?

PHPycho

4:34 am on Oct 27, 2009 (gmt 0)

Hello forums!

How to query in following case:
Suppose we have two tables
--------------
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.

PHPycho

5:28 am on Oct 27, 2009 (gmt 0)

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 )

PHPycho

8:11 am on Oct 27, 2009 (gmt 0)

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 = ?)

PHPycho

11:22 am on Oct 27, 2009 (gmt 0)

knock knock

PHPycho

10:08 am on Oct 28, 2009 (gmt 0)

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.