sql to Combine 3 tables

[terp_in_umcp]

Hi there...

Heres the situation...I have 3 tables:

Table1: Key=number; condition to check=number='10' and Active='1'
Table2: Key=Number; condition to check=Number='10' and IsActive='1'
Table3: Key=AcctID; condition to check=AcctID='10' and Active='1'

Now all I am trying to do is get data from all these 3 tables...something like this:

Select * from Table1 where number='10' and Active='1'
and
Select * from Table2 where Number='10' and IsActive='1'
and
Select * from Table3 where AcctID='10' and Active='1'

Although, as you can see, all 3 tables have a different name for the same column(Table1.number=Table2.Number=Table3.AcctID).

I dont really need all the columns from the tables, but just need to know if the query returns a record or not..

Any ideas?

[Fumigator]

You can use a subselect with the "exists" clause. You can either use "select count(*)" and then check the result to see if it's > 0, or you can select a single column and then check mysql_num_rows() to see is that is > 0.

SELECT number
FROM Table1 as t1
WHERE exists (SELECT 1 FROM Table2 as t2 WHERE t2.Number = t1.number and t2.IsActive = '1')
AND exists (SELECT 1 FROM Table3 as t3 WHERE t3.AcctID = t1.number and t3.Active = '1')

[terp_in_umcp]

Hey Fumi,

Thanks for your feedback however tht query seems to only pull the number column from the Table1 and check to see if that exists in the other tables...

What I was trying to do is, to see if thats particular Account (number/Number/AcctID='10') exists in any of the tables and if it does then say Count=1. If none of the tables have a record for tht account, then Count=0...

More ideas?
Thanks...

[Fumigator]

So it's really three different queries, you just want to combine them. I guess you could use a union, but each table will give you its count in a separate row so you'll need to fetch through them.


SELECT 'table1 count', count(*)
FROM Table1 as t1
WHERE t1.number = '10'
UNION ALL
SELECT 'table2 count', count(*)
FROM Table2 as t2
WHERE t2.Number = '10'
UNION ALL
SELECT 'table3 count', count(*)
FROM Table3 as t1
WHERE t3.AcctID = '10'

[terp_in_umcp]

Thanks man..that totally works:)

Hey, tell me a good tutorial where I can read abt unions and odd joins...really need to clear my basics:)