Home > Archive > PHP SQL > October 2005 > get data from 2 tables, 1 is a count
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
get data from 2 tables, 1 is a count
|
|
|
|
I have a customer ID for both tables..
table 1 contains customer info
table 2 contains items everyone owns
I can get the info from table one select on id.
I can get a count of items on table 2 count(*) on id
But I want to do this in one query so i can show results in a table
and even order by results of table one or two
What I have is
select t1.count(*) as count,t2.*, from t1,t2 order by count
But that gives me an error pointing to (*)
Can anyone give me the proper syntax plus explanation.. please:)
| |
| none@nomail.com 2005-10-26, 9:56 pm |
| On Thu, 27 Oct 2005 00:54:42 +0100, in alt.php.sql you wrote:
>What I have is
>select t1.count(*) as count,t2.*, from t1,t2 order by count
>
>But that gives me an error pointing to (*)
>
>Can anyone give me the proper syntax plus explanation.. please:)
damm forgot the where
ID=x
select t1.count(*) as count,t2.* from t1,t2 where t1.id=ID and
t2.id=ID order by count
| |
| dead@deadmail.com 2005-10-26, 9:56 pm |
| On Thu, 27 Oct 2005 01:07:22 +0100, in alt.php.sql you wrote:
>On Thu, 27 Oct 2005 00:54:42 +0100, in alt.php.sql you wrote:
>
>
>
>
>damm forgot the where
>ID=x
>
>select t1.count(*) as count,t2.* from t1,t2 where t1.id=ID and
>t2.id=ID order by count
got it working nearly :(
select count(t1) as count,t2.* from t1,t2 where t1.id=ID and
t2.id=ID group by t1.id order by count
BUT this only shows results from table 1 that also have soemhting in
table two... I need to show all results of table one even if nothing
in table two... i have tried replacing and with or ... that gives me
all results.. but the count is wrong :(
any help appreciated ? :)
| |
| Stefan Rybacki 2005-10-27, 7:57 am |
| dead@deadmail.com wrote:
> On Thu, 27 Oct 2005 01:07:22 +0100, in alt.php.sql you wrote:
>
>
>
>
>
> got it working nearly :(
>
> select count(t1) as count,t2.* from t1,t2 where t1.id=ID and
> t2.id=ID group by t1.id order by count
>
> BUT this only shows results from table 1 that also have soemhting in
> table two... I need to show all results of table one even if nothing
> in table two... i have tried replacing and with or ... that gives me
> all results.. but the count is wrong :(
>
> any help appreciated ? :)
Use a LEFT JOIN!
select count(t1) as count,t2.* from t1 LEFT JOIN t2 ON (t1.id=ID and
t2.id=ID) group by t1.id order by count
Regards
Stefan
| |
| not_on@dummyaddress.com 2005-10-30, 6:58 pm |
| On Thu, 27 Oct 2005 11:18:21 +0200, Stefan Rybacki
<stefan.rybacki@gmx.net> wrote:
[color=darkred]
>Use a LEFT JOIN!
> select count(t1) as count,t2.* from t1 LEFT JOIN t2 ON (t1.id=ID and
> t2.id=ID) group by t1.id order by count
That now works perfectly.. many thanks.
Why will it not accept a wildcard in count such as count( t1.*)
instead it needs a collum name.. not important.. just unexpected:)
I do have MySQL by Paul DuBois and its looking a bit ragged now I
have used it so much.. I just can't seem to get my head around the
join ..erk :( More reading and more testing I guess.
Many many thanks for your answer Stefan . Much appreciated.
|
|
|
|
|