do I need to use JOIN?

do I need to use JOIN?

Post by Adam Balcomb » Sat, 05 Feb 2000 04:00:00



Hi,
I've got three tables from the same database which are not related. In
each table there is a column called 'complete', which basically sets a
flag telling me whether a user has made an entry or not. I want to
select all rows where the column 'complete' has been set to 1 in each of
the three tables, to give me an overall figure.
e.g. table 1...complete is set to 1 on 5 rows
       table 2...complete is set to 1 on 3 rows
       table 3...complete is set to 1 on 2 rows

so the result I would be hoping for is a record count of 10.

I can't get either the syntax or the method correct. I'm using a web
front end, SQL Server version 7 (NT), and Cold Fusion as the middleware.
Has any got any ideas....much appreciated.

Adam.

 
 
 

do I need to use JOIN?

Post by Marcin Malinowsk » Sat, 05 Feb 2000 04:00:00


Hi Adam,

try this
select complete from table1 where complete=1
union
select complete from table2 where complete=1
union
select complete from table3 where complete=1

hope this helps
Marcin


> Hi,
> I've got three tables from the same database which are not related. In
> each table there is a column called 'complete', which basically sets a
> flag telling me whether a user has made an entry or not. I want to
> select all rows where the column 'complete' has been set to 1 in each of
> the three tables, to give me an overall figure.
> e.g. table 1...complete is set to 1 on 5 rows
>        table 2...complete is set to 1 on 3 rows
>        table 3...complete is set to 1 on 2 rows

> so the result I would be hoping for is a record count of 10.

> I can't get either the syntax or the method correct. I'm using a web
> front end, SQL Server version 7 (NT), and Cold Fusion as the middleware.
> Has any got any ideas....much appreciated.

> Adam.


 
 
 

do I need to use JOIN?

Post by Adam Balcomb » Sun, 06 Feb 2000 04:00:00


Worked fine when used with ALL operator. Thanks very much for your help!!!

> Hi Adam,

> try this
> select complete from table1 where complete=1
> union
> select complete from table2 where complete=1
> union
> select complete from table3 where complete=1

> hope this helps
> Marcin


> > Hi,
> > I've got three tables from the same database which are not related. In
> > each table there is a column called 'complete', which basically sets a
> > flag telling me whether a user has made an entry or not. I want to
> > select all rows where the column 'complete' has been set to 1 in each of
> > the three tables, to give me an overall figure.
> > e.g. table 1...complete is set to 1 on 5 rows
> >        table 2...complete is set to 1 on 3 rows
> >        table 3...complete is set to 1 on 2 rows

> > so the result I would be hoping for is a record count of 10.

> > I can't get either the syntax or the method correct. I'm using a web
> > front end, SQL Server version 7 (NT), and Cold Fusion as the middleware.
> > Has any got any ideas....much appreciated.

> > Adam.

 
 
 

1. Joins - What I'am doing wrong

Hello,

I've made a query which looks like this:

select P.*, R.Name Region, M.Name Manufacturer from Products P
left join Regions R on (P.RegionID = R.ItemID)
left join Manufacturers M on (P.ManufacturerID = M.ItemID)
where (P.ItemID = 321)

This query works fine - it lists the specified product, together with the
Region and Manufacturer.
However, the more joins we add (another 10 joins are required) the longer it
takes to get the result - when all required joins are included it takes 15
seconds!
What am I doing wrong or is this just normal behaviour? Most of the lookup
tables (Regions, Manufacturers, Applications, etc...) are very small tables
(max 100 rows)
Thanks,

Alain Sienaert
Cortex
Belgium

2. PowerBuilder / Oracle Developers - SF, CA (Rec)

3. Can this be done?.... Join Question

4. 7.3.4.0.0 and Linux

5. Where should joins be done?

6. ObjectDRIVER 1.1.124 new release

7. Doing joins with OpenQuery

8. DTS and Dr Watson

9. Doing Join, how to reference common fields?

10. Problem doing OUTER joins w/ pdox tables

11. Outer Join - doing it first

12. Self-Join: What am I doing wrong?

13. Rewrite Query using Inner Join, Left Join e.t.c