distinct count

bimmer528

Hey all, I am having a problem pulling data here. It is taking a very long time and was wondering if I was doing it wrong here.

select t1.f1, t1.f2, t2.f1 from table1, table2 AS t1, t2
GROUP BY t1.f1
Having t1.f1=t2.f1

Is there a better way to do this?

25151 rows in t1
1200 rows in t2

obsidian

what exactly are you trying to pull from the records? this seems a bit verbose, and i'm sure you could optimize it, but i hesitate to post a suggestion without understanding exactly what you're trying to accomplish.

bimmer528

I am trying to match all entries from table 1 field 1 to table 2 field 1 with both fields in both both tables grouped

basically being able to pull data from both tables while each correctly joins by a field

pull data from f1,f2,f3 from table1 and pull data from f1,f2,f3 from table2 where f1 from each table are equal to each other.

Both feilds from each table must also be grouped.

I have it working correctly, it just takes forever to query.

obsidian

i think that it's the HAVING that's slowing you down since it's not doing the comparison until after everything else has been joined together. would something like this work:

select t1.f1, t1.f2, t2.f1 from table1, table2 AS t1, t2 WHERE t1.f1 = t2.f1
GROUP BY t1.f1

don't know if that throws off your grouping or not, though

bimmer528

Where takes just as long. The query is fast until I add the GROUP BY but I need both tables grouped and combined by field 1

TF_TheMoon

Your group by isn't going to give you unique values and Joining in the having clause is what's slowing you down

SELECT
t1.f1,
t1.f2,
t2.f1
FROM
table1 AS t1
LEFT JOIN table2 AS t2 ON t1.f1 = t2.f2

If you only want records where they existi in both tables replace the LEFT JOIN with an INNER JOIN

Also, if you only want distinct rcords of t1.f1, t1.f2, t2.f1 then either do
SELECT DISTINCT rather than SELECT

or GROUP BY t1.f1, t1.f2, t2.f1