how to perform such in single query??

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

how to perform such in single query??

Post 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.
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Re: how to perform such in single query??

Post 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 )
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Re: how to perform such in single query??

Post 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 ??
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Re: how to perform such in single query??

Post by PHPycho »

Knock Knock.. anybody there..
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: how to perform such in single query??

Post 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! :D
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Re: how to perform such in single query??

Post 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.
Post Reply