Two Select statements without UNION

Question

Two Select statements without UNION

Minnu

SSCertifiable

Points: 6451
More actions
November 9, 2016 at 2:43 am

#314775

I have two tables
Dept table with 8 records
Logins table with 23 records
below query should display 31 records, but it is displaying 184 records (8*23=184).
i want below query to display 31 records without using UNION - Please suggst
select d.name from dept d, logins l
where d.dept_id='ICT' and l.login_id=10

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Minnu (11/9/2016)
I have two tables
Dept table with 8 records
Logins table with 23 records
below query should display 31 records, but it is displaying 184 records (8*23=184).
i want below query to display 31 records without using UNION - Please suggst
select d.name from dept d, logins l
where d.dept_id='ICT' and l.login_id=10

Can you post the DDL (create table) scripts, sample data as an insert statement and the expected results please?

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

There's no join between the two tables, so you are getting a cartesian product, which will indeed result in more rows than you want.

If there's no relation between the two, and you want a list that contains all the names in dept and all the names in login, that's what UNION/UNION ALL does, and a join is not a substitute.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Minnu SSCertifiable Points: 6451 More actions · Answer 3

i have below two tables,and am trying to create a view using below statements.

also wants to apply index on view, i cannot apply index on views with UNION.

so i want to combine query into single statement without union

select login_name from logins where login_name='Unify'

union

select login_name from HHT where HHT_Login='ICH'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

As was mentioned in the other thread on this same topic, your options here are limited. You want to do a UNION, without using UNION and there isn't an obvious single statement for that (if you need to use UNION, use UNION)

Why do you need an indexed view? They're typically niche solutions, not something mainstream.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

Minnu (11/9/2016)
i have below two tables,and am trying to create a view using below statements.
also wants to apply index on view, i cannot apply index on views with UNION.
so i want to combine query into single statement without union
select login_name from logins where login_name='Unify'
union
select login_name from HHT where HHT_Login='ICH'

For such simple query you don't need an index. Even if there are couple of joins you'll sill not need an index.

Having index on a view is good when you have some couple of aggregations.

Igor Micev,My blog: www.igormicev.com