how to perform such in single query??

[PHPycho]

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]

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 )

[technica]

How about this?


SELECT ...
FROM table1
Left Outer JOIN table2 on table1.id=table2.id
Where table1.FIELD > 0
and table1.FIELD = table2.FIELD
and table1.id = table2.table1_id

You can also try if table1.id do not have any reference with table2.id




SELECT ...
FROM table1, table2
Where table1.id=table2.id
and table1.FIELD > 0
and table1.FIELD = table2.FIELD
and table1.id = table2.table1_id



Hope this helps you

[Coyote6]

Don't know if this is what you are looking for or if this even works but off of the top of my head this is what I came up with.


SELECT b.id, b.title, b.table1_id, b.field FROM table2 as b JOIN table1 as a ON (a.field=b.field) WHERE a.field>0
UNION
SELECT b.id, b.title, b.table1_id, b.field FROM table2 as b JOIN table1 as a ON (a.id=b.table1_id) WHERE a.field<0 && a.id=some_value

[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 ??

[Old Pedant]

That last version of yours looks the cleanest, PHP.

If table1.field might be *EITHER* null or zero, then you could do:


SELECT ...
FROM table1
INNER JOIN table2
ON
( IFNULL(table1.field, 0) = table2.field )
OR
( IFNULL(table1.field, 0) != 0 table1.id = table2.table1_id AND table1.table1_id = ?)

But if you know it is null, never zero, then your version looks like the best idea.

[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.

[Old Pedant]

Looks to me like you had it.


SELECT P.id AS productID, P.shop_id, S.shop_title, S.admin_id, P.title AS productTitle
FROM shops AS S, products AS P
WHERE ( S.admin_id <> 0 AND S.admin_id = P.admin_id )
OR ( S.admin_id = 0 AND S.id = P.shop_id )


*BUT*

But as I read that--and as I see your tables--I think your results will be

productID :: shop_id :: shop_title :: admin_id :: productTitle
2 :: 1 :: shop 1 :: 10 :: product2
3 :: 1 :: shop 1 :: 10 :: product3
4 :: 2 :: shop 2 :: 0 :: product4

That is, you will *NOT* see product 1.

Because shop 1 *does* have an admin_id > 0 but product 1 does not have an admin_id that matches shop 1.

Product 2's admin_id of 10 matches the admin_id of shop 1, so the shop_id is not used.

Product 3's admin_id of 10 matches the admin_id of shop 1, so the shop_id is not used.

The ONLY product that can match shop 2 is product 4, because shop 2 has an admin_id of 0 and product 4 has an admin_id of 0 and so the shop_id is used.

I strongly suspect you haven't given us the right examples or results, or you would have been happy with your own query by now.