if condition in query

[delickate]

Hi,

Can we use 'if condition' in query before where clause??? or is there any alternate way to do following thing?

 

SCENARIO:

-------------

 

i've two tables

 

__________

tbl_profile

----------------

pro_id

pro_name

__________

 

 

_________

tbl_view

--------------

v_id

v_one

v_two

v_detail

__________

 

 

tables has following content

 

tbl_profile

--------------------------------

pro_id            pro_name

1                    sani

......

.....

..

 

 

tbl_view

v_id            v_one            v_two        v_detail

1                    2                  3                  testing detail

2                    1                  3                  testing detail A

3                    2                  1                  testing detail B

4                    6                  4                  testing detail

5                    7                  5                  testing detail

 

 

profile id could come as foreign id in table view in two fields v_one and v_two.

as you can c in above table. id '1' is as foreign in v_one and v_two against v_id 2 and 3 respectively.

 

so i want to query that if profile id is found in v_one or v_two it should join. so i'm trying following query

 

select * from tb_profile p  left join tbl_countries c on (p.p_country = c.countries_id)

                                   

if((select * from tbl_view where v_one = '1') > 0)

left join tbl_view v i on (p.pro_id = v.v_one)

 

if((select * from tbl_view where v_two = '1') > 0)

left join tbl_view v i on (p.pro_id = v.v_two)

 

is it a fine way to or we could different query for that?

Please help

Looking forward

 

Thanks

[Barand]

Have you considered normalising your data?

[delickate]

Hi,

Thanks for the reply.

Yes, actually view table is like a relation table. many things could relate to many other things.

ok forget it. can we get two specific id with one query ?

means i've 10 record in table.

I want to get 4 and 6 record only

suppose i query for it like this

 

select * from tbl_profile p where 1=1 and p.pro_id = '33' and p.pro_id = '56'

but this query doesn't show the record. my requirement is not two use 'or'

Please help on this

 

Thanks

[Barand]

You can use the table twice with different aliases

 

SELECT a.pro_name as name1, b.pro_name as name2
FROM tbl_view v
LEFT JOIN tbl_profile a ON v.v_one = a.pro_id
LEFT JOIN tbl_profile b ON v.v_two = b.pro_id

[cpd]

You can use the table twice with different aliases

 

SELECT a.pro_name as name1, b.pro_name as name2
FROM tbl_view v
LEFT JOIN tbl_profile a ON v.v_one = a.pro_id
LEFT JOIN tbl_profile b ON v.v_two = b.pro_id

 

I believe thats unnecessary as you can just use an OR statement in the initial join as follows.

 

SELECT a.pro_name as name1, b.pro_name as name2
FROM tbl_view v
LEFT JOIN tbl_profile a ON v.v_one = a.pro_id OR v.v_two = a.pro_id

 

[delickate]

Thanks guys,

It is really helpful and solved my problem. i really appreciate your all assistance.

Thanks again.

[Barand]

CPD,

 

Depends on how you want the results returned. Also any significance of which name belongs to v_one and to v_two is lost in your solution For example, 'one' could be lead and 'two' the assistant, or maybe a fixtures list with home and away teams.

[cpd]

CPD,

 

Depends on how you want the results returned. Also any significance of which name belongs to v_one and to v_two is lost in your solution For example, 'one' could be lead and 'two' the assistant, or maybe a fixtures list with home and away teams.

 

Yes I see what your saying. You could include the v_one column and v_two columns but that may be a little unnecessary.