0

I've been looking for this but I didn't found what I need. I'm creating an admin page and I need to access the db values from a php interface.

I need to get all the rows from many tables. First I tried with:

SELECT * FROM t1, t2, t3, t4

But that's duplicating rows many times, something odd... The first table have 8 rows, the second 8 too, the third 7 and the fourth 1. There are 24 rows in total, but if I execute that query I get 448 rows... why?

Let's say that I have 4 tables and I want to get all that data in one query, get the columns in the order I put the tables in the query but without duplicate... There are no conditions, just get all the data like if I make 4 queries:

SELECT * FROM t1
SELECT * FROM t2
SELECT * FROM t3
SELECT * FROM t4

If I do this no data is duplicated... why?

How would I merge those 4 queries but still getting the same results? (no duplicated data)

Chazy Chaz
  • 1,781
  • 3
  • 29
  • 48
  • `SELECT * FROM t1, t2, t3, t4` creates implicit `JOIN' between the 4 tables. Since you apply no filters it translates to Cartesian Product of the four. – PM 77-1 Dec 23 '15 at 22:16
  • 1
    If all tables have the same structure, use [`UNION`](http://www.techonthenet.com/sql/union.php). – PM 77-1 Dec 23 '15 at 22:18
  • Ah right, I figured that something like that was happening but I never heard of the Carteasian Product. No, they are different tables. I could just make 4 queries but I'd like to know how can I do this. – Chazy Chaz Dec 23 '15 at 22:19
  • Why doesn't `UNION` do what you want? – Barmar Dec 23 '15 at 22:45
  • You're executing a `cross join`. Don't do that! - http://www.w3resource.com/mysql/advance-query-in-mysql/mysql-cross-join.php – Gordon Freeman Dec 23 '15 at 23:14
  • Think about the column names in each table. If they're different, what can you expect the results to be when you query all four of them; how could you retrieve the values from the result set? – miken32 Dec 23 '15 at 23:16
  • @miken32 it makes sense if the tables are joining... so is there any way to select the data without joining the tables? – Chazy Chaz Dec 23 '15 at 23:39
  • Possible duplicate of [Cross Join without duplicate combinations](http://stackoverflow.com/questions/12932045/cross-join-without-duplicate-combinations) – Gordon Freeman Dec 23 '15 at 23:41
  • But in a join, the fields in each row of the result set are related in some way. There's no relation here. You have to have four queries. What's the problem with four queries? – miken32 Dec 24 '15 at 00:48
  • No problem at all, I just thought I could get the data in 1 query. I'll make as many as I'll need. – Chazy Chaz Dec 24 '15 at 00:52

1 Answers1

-1

Like I said in the comment; you're executing a cross join. Don't do that!

But in general, if you want to exclude duplicates, you could SELECT DISTINCT ...

Gordon Freeman
  • 3,260
  • 1
  • 22
  • 42