Multiple table search

[Ivan Smahin]

Hello Gregory,

Wednesday, March 30, 2005, 7:16:17 AM, you wrote:

GK> As an example lets say I have two table with following content: :

GK> aTable

GK> fieldone       fieldtwo
GK> 1001             1002



GK> bTable
GK> fieldthree     fieldfour
GK> 1003             1004

GK> I want to query fieldtwo and fieldfour at once for a value (1004).

In other words you want to select some records from both tables
which have the same values in fieldtwo and  fieldfour fields and
this value should be 1004.


select *
FROM
    aTable, bTable
where
     fieldtwo = fieldfour
     and fieldtwo = 1004

Another syntax for this query is:

select *
FROM
    aTable INNER JOIN bTable ON fieldtwo = fieldfour
where
     fieldtwo = 1004


Or you want just collect some records from both tables which have 1004 in some fields.
There is rather different query, because here is nothing like join is needed.
UNION seems to be appropriate solution:

     
select *
FROM
    aTable
where
     fieldtwo = 1004
union
select *
FROM
    bTable
where
     fieldfour = 1004
     

-- 
Best regards,
 Ivan                            mailto:IvanSmahin at public.kherson.ua