Do I Need A Join?

[joeygallo]

I have a table that looks like this:

 

TABLE1:

id, username, timestamp

1, test1, 123123123

2, test2, 123123124

3, test3, 123123125

4, test4, 123123126

1, test1, 123123127

1, test1, 123123128

3, test3, 123123129

 

There are thousands of entries in that table.  I'm producing a top 10 list of entries based on row count and date range using this:

 

select id, COUNT(id) AS total_count from TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime' GROUP BY id ORDER BY total_count DESC LIMIT 10

 

startTime and endTime is defined and the above query works exactly how I want it to.  Now I want to add another table into the mix.

 

TABLE2:

id, username, timestamp

1, test1, 123123123

2, test2, 123123124

3, test3, 123123125

4, test4, 123123126

 

How can I include TABLE2 into the query so the totals come from both tables and are combined?

[ManiacDan]

select id, COUNT(id) AS total_count from ((SELECT * FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime') UNION (SELECT * FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime')) GROUP BY id ORDER BY total_count DESC LIMIT 10

[Christian F.]

Depending upon what the IDs refer to, you might need a JOIN or a UNION. I can't really tell from what little info you've posted above.

[joeygallo]

I replaced this:

from TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime'

 

With this as suggested:

from ((SELECT * FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime') UNION (SELECT * FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime'))

 

The output is this:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given

 

The IDs are just user IDs.  They are not indexed or auto-incremented.. just a numerical value.

 

Both TABLE1 and TABLE2 have the timestamp and id fields in them.  It's basically a matching table with a different name.

[joeygallo]

Trying to figure this out on my own I read this about UNIONS:

The number of columns in each SELECT statement has to be the same.

 

TABLE1 has 33 columns, TABLE2 has only 30 columns.

 

I will continue working on this.

[joeygallo]

The UNION is not working.  The tables don't have the same number of columns and I can't change that as one table has additional data than the other.

 

EDIT: They do both have the ID and TIMESTAMP fields as mentioned before and they are both setup the same as far as the field types.  Any suggestions on why this doesn't work?

[DavidAM]

Trying to figure this out on my own I read this about UNIONS:

The number of columns in each SELECT statement has to be the same.

 

TABLE1 has 33 columns, TABLE2 has only 30 columns.

 

I will continue working on this.

 

Don't use SELECT * in the UNION queries. Just select the common columns that you are working with

 

from ((SELECT id, username, timestamp FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime') 
      UNION (SELECT id, username, timestamp FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime'))

[joeygallo]

Don't use SELECT * in the UNION queries. Just select the common columns that you are working with

 

from ((SELECT id, username, timestamp FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime') 
      UNION (SELECT id, username, timestamp FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime'))

 

This works:

SELECT id, COUNT(id) AS total_count FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime' GROUP BY id ORDER BY total_count DESC LIMIT 10

 

And this works:

SELECT id, COUNT(id) AS total_count FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime' GROUP BY id ORDER BY total_count DESC LIMIT 10

 

SELECT id, COUNT(id) AS total_count FROM ((SELECT id FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime') UNION (SELECT id FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime')) GROUP BY id ORDER BY total_count DESC LIMIT 10

 

So independently they are working but together in the JOIN they are not.  I tried select id, timestamp in both queries instead of just id.  That didn't make a difference.  I'm just not seeing what the problem is here.

[joeygallo]

I think I'm getting somewhere.  I run this direct on the database (just basic queries with no extra stuff added):

SELECT id FROM ((SELECT id FROM TABLE1) UNION (SELECT id FROM TABLE2))

 

I get this:

Every derived table must have its own alias

[ManiacDan]

SELECT id FROM ((SELECT id FROM TABLE1) UNION (SELECT id FROM TABLE2)) AS Foo

[joeygallo]

SELECT id FROM ((SELECT id FROM TABLE1) UNION (SELECT id FROM TABLE2)) AS Foo

 

I tried that.  No more error but the output is not correct.  Here's the current code.

 

$get_totals = mysql_query("SELECT id, COUNT(id) AS total_count FROM ((SELECT id FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime') UNION (SELECT id FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime')) as x GROUP BY id ORDER BY total_count DESC LIMIT 10");
if (mysql_num_rows($get_totals)) {
while ($qry = mysql_fetch_array($get_totals)) {

// display info here.
// $qry['total_count'] is used to show number of entries.
} }

 

In the above output, the accounts are not ordered correctly and the value of the qry['total_count'] variable simply reads 1 for each entry found.

[Barand]

if you GROUP BY id and COUNT(id) what total, other than 1, would you expect?

[ManiacDan]

You're only selecting the ID from the inner tables.  Perhaps you're not getting the right number.  Try this:

 

 

$get_totals = mysql_query("SELECT id, COUNT(timestamp) AS total_count FROM ((SELECT id, timestamp FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime') UNION (SELECT id, timestamp FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime')) as x GROUP BY id ORDER BY total_count DESC LIMIT 10");

 

If that doesn't work, do the inner query only (table X) and see if there are any duplicates.  Maybe there just aren't.

 

[fenway]

I think I'm getting somewhere.  I run this direct on the database (just basic queries with no extra stuff added):

SELECT id FROM ((SELECT id FROM TABLE1) UNION (SELECT id FROM TABLE2))

 

I get this:

Every derived table must have its own alias

 

Also, why the extra select at all?

[Barand]

UNION will ignore duplicates. If you want all rows use UNION ALL

[ManiacDan]

@fenway, I was unioning the two tables in the inner select so that the counts were accurate

 

@barand:  That's exactly right, I completely forgot about union all.  That's the issue.  You can fix it either with UNION ALL or by including the timestamp in the inner query.

[joeygallo]

Thank you to all contributors on this thread.  I have the SQL query working as needed.