Trying to build a query...

A

Anonymous

Guest
Hi everybody. This is my first post here :p

I'm trying to build a query to get some info from 3 tables at the same time, but I have some problems.

Let's suppose that these are the tables:

table1
id1
userid
id2
name1
...

table2
id2
name2
...

table3
id3
id1
...


Knowing the value of userid...

(1) I want to get all records from table1 with a certain value in the userid field. Ok, I've done this:

SELECT * FROM table1 WHERE userid = '$userid'


(2) I want to get the value of table2.name2 for each of the record I get with the previous query. I've done this:

SELECT * FROM table1, table2.name2 WHERE userid = '$userid' AND table1.id2 = table2.id2


(3) For each record I get with the previous query I want to COUNT all the rows in table3 where table3.id1 = table1.id1

Here is my problem. I tried with COUNT but it doesn't work. Am I doing something wrong or is not possible to do that?

Can anyone help me?

Thanks in advance :)

Sergio
 
Code: 
SELECT table1.*, table2.*, count(table3.id) FROM table1, table2, table 3 WHERE userid = '$userid' AND table1.id2 = table2.id2 AND table1.id1 = table3.id1 group by table1.id1

you may need to add some extra fields to the group by part to make sure you get the results you want.

Greetz Daan
 
Thank you very much! :D

I don't know why, but I had the feeling it was not possible to use the GROUP BY clausule in PHP :roll:
 
indomito said:
I don't know why, but I had the feeling it was not possible to use the GROUP BY clausule in PHP :roll:
Click to expand...
Every query that mysql accepts can be run with PHP. PHP simply passes it on to mysql.

Greetz Daan
 
Yes, sorry. I meant MySQL, not PHP. I though I read somewhere that you couldn't use that. Good to know I can use it :)
 
Hi again.

Daan, I have a little problem with the query you gave me. It works perfectly for what I need except for one thing.

Using the database structure that I post in my first message I would like to get all records in table1 from a single user. I know the userid but my problem is that using this query...

Code: 
SELECT table1.*, table2.*, count(table3.id) FROM table1, table2, table 3 WHERE userid = '$userid' AND table1.id2 = table2.id2 AND table1.id1 = table3.id1 group by table1.id1

...I don't get the records that doesn't have a corresponding record in table3. For example, imagine that I store my customers in table1 and table3 is the table where I store each item I sell. If a user haven't bought anything yet, I will not get the user info using the previous query. Is there a way to get the user info even if he haven't bought anything? I've been trying with JOIN clausules but can't get them working. I would like to keep getting all the info I get with the previous query.

Any help would be appreciated :)

Cheers,
Sergio
 
indomito said:
Daan, I have a little problem.......
Click to expand...

Daan isn't posting at this forum anymore.

Can't tell you where he went, the moderators and admin of this site don't want that to be public. (they didn't kill him, I can tell you that)

As for your question, I know that removing table3 from your query will return all users. you'll lose the info from table3.
I'm not that good at mysql myself so I can't really help you with that.
 
OK, I will call the A-Team and try to find him :D Thanks for your interest.
 
The question has been answered in under 10 minutes with one happy visitor, if you want proof you can come and see for yourself
 
You must log in or register to reply here.
Back
Top