Problem with Select Union

[leerobbo]

Hello all

 

I'm attempting to retrieve data from two tables and while it is working to an extent, a number of rows of data fail to display. An example statement I've been using is ' SELECT home, away, ftr FROM eng1011 WHERE home = 'Liverpool' OR away = 'Liverpool' UNION SELECT home, away, ftr FROM eng0910 WHERE home = 'Liverpool' OR away = 'Liverpool';

 

That would display 67 rows of data but the problem is there should be 76 rows of data. The rows of data missing are from the second table and if the tables are swapped around so 0910 table is the first select statement then the 1011 table would have the rows of data missing.

 

I don't really know what should be done here so any help is appreciated thanks.

[fenway]

UNION is implicitly UNION DISTINCT.

 

If you don't want that, use UNION ALL.

[The Little Guy]

What I commonly do when doing a union is add another column, and fill it with what table the row came from, not only does it give all of the rows, but now you have information on the table.

 

select column_from_table_1 as col1, 'table1' as table from table_1 where something = 'some value'
union
select column_from_table_2 as col1, 'table2' as table from table_2 where something = 'some value'

 

Note: I am not sure if this is a better solution than UNION ALL (probably not), but it is helpful IMO.

[fenway]

That has absolutely nothing do with with the "missing" rows -- they're missing because they're not distinct.  MySQL thinks it's doing you a favour.  That will indirectly make them non-distinct -- but that's not robust.

[leerobbo]

I've just tried the UNION ALL on the MySQL command line and it works a treat. Thank you very much fenway!

[The Little Guy]

That has absolutely nothing do with with the "missing" rows -- they're missing because they're not distinct.  MySQL thinks it's doing you a favour.  That will indirectly make them non-distinct -- but that's not robust.

 

Yes it does, they are usually no longer distinct if you add that column

 

Not Distinct:

1, table1

1, table2

 

removing what table they came from make them distinct

[fenway]

That has absolutely nothing do with with the "missing" rows -- they're missing because they're not distinct.  MySQL thinks it's doing you a favour.  That will indirectly make them non-distinct -- but that's not robust.

 

Yes it does, they are usually no longer distinct if you add that column

 

Not Distinct:

1, table1

1, table2

 

removing what table they came from make them distinct

You're missing the point --- MySQL is still running DISTINCT, and wasting it's time.  The fact that you might want to know what table they originated from is not the issue.