CODEWITHSUNDEEP
sqlmysqloracle-database
Shahzaib Khan
Shahzaib Khan

Reputation: 23

sql select statement giving more rows then records in tables

The error is very simple.

There are 3 Tables,

Lets name them A, B, C - All of them have 7 rows.

A has the following data

1 2 3 4 5 6 7

with Id = 1

B has the following data

8 9 10 11 12 13 14

with Id = 1

C has the following data

15 16 17 18 19 20 21

with Id = 1

Now i am selecting them as : select A.col1,B.col1,C.col1 from A,B,C where A.ID= B.ID and C.ID= A.ID and B.ID= C.ID.

So the output thats coming is 343.

now what i want the output to be is simply number from 1 to 21 like :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Please help me get out of this trouble and suggest me a fine tune query.

Upvotes: 1

Views: 613

Answers (4)

kevpie
kevpie

Reputation: 26108

You can use a subquery like this to reduce the number of places you have to state col1 = X to one.

SELECT col1 FROM 
(
  (SELECT id, col1 FROM A)
  UNION ALL
  (SELECT id, col1 FROM B)
  UNION ALL
  (SELECT id, col1 FROM C)
) AS ABC 
WHERE id = 5;

The query optimizer should be able to apply the filter appropriately on each table before the UNION ALL is performed.

EDIT: Fixed to constrain by id as hilighted by @jeffrey-kemp)

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50037

Given your query that's exactly what you should get - 343 rows ( = 7 * 7 * 7). Because all the rows in each table have the same ID (1) the query in your question is equivalent to doing a Cartesian join of the tables, i.e. a query with no WHERE clause where all the rows in each table are each joined to all the rows of the other tables - something like

SELECT A.COL1, B.COL1, C.COL1
  FROM A, B, C;

As others have suggested, a UNION is a good way to get what you want as it seems you're not really trying to join information from the rows together:

SELECT COL1 FROM A
UNION ALL
SELECT COL1 FROM B
UNION ALL
SELECT COL1 FROM C;

Share and enjoy.

Upvotes: 0

Brian
Brian

Reputation: 6450

You could maybe use UNIONs? e.g. if it's ID 5 you want to pull stuff back with:

SELECT col1
FROM A
WHERE ID = 5
UNION
SELECT col1
FROM B
WHERE ID = 5
UNION
SELECT col1
FROM C
WHERE ID = 5;

This will put them all in a single column, rather than giving you a 7 x 7 x7 combination

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132630

Try:

select col1 from a
union
select col1 from b
union
select col1 from c

Upvotes: 3

Related Questions