[RESOLVED] LEFT JOIN Trouble! (im quite the newbie)

[Xafro]

Hey phpbuilder.com,

this may be a relative easy problem to solve but its getting me a massive headache!
First off i will tell you what my database looks like, i have three tables:

Table name: auto (Translated for you: car)

---

| a_id a_type g_id

---

| 1 | Porsche 911 | 3 |

| 2 | Honda civic | 2 |

Table name: gebruikers (Translated for you: users)

---

| g_id a_username g_wachtwoord (translated: password)

---

| 1 | administrator | poodle |
| 2 | user1 | pug |

| 3 | user2 | coffee |

Table 3: rating

---

| a_id g_id score

---

| 1 | 2 | 5 |

| 1 | 1 | 3 |

OK so here's my query i use to get all the data out:

$query = "SELECT * FROM auto
		  LEFT JOIN gebruikers ON auto.g_id = gebruikers.g_id
		  LEFT JOIN rating ON auto.a_id = rating.a_id
		  ORDER BY a_type";

And this results in:

---

| a_id a_type score g_id

| 1 | Porsche 911 | 5 | 3
| 1 | Porsche 911 | 3 | 3

| NULL| Honda civic | NULL | 2

My questions:

How can i make it so that if a car is rated twice that i only get 1 row with the
score averaged out? like this: 1 Porsche 911 4 3. i tried using AVG() but i get
a syntax error each time i try.

Why is the a_id on non-rated cars NULL? how can i fix this?

Thanks in advance!
Xafro

[tomhath]

AVG() is what you want, should be something like this. When using aggregate functions like AVG() it's better style to list out the columns you are selecting instead of using *, and GROUP BY all of them.

SELECT auto.a_id, auto.a_type, avg(rating.score) as score, gebruikers.g_id
FROM auto
LEFT JOIN gebruikers ON auto.g_id = gebruikers.g_id
LEFT JOIN rating ON auto.a_id = rating.a_id
GROUP BY auto.a_id, auto.a_type, gebruikers.g_id
ORDER BY auto.a_type

[Xafro]

tomhath;10897825 wrote:

AVG() is what you want, should be something like this. When using aggregate functions like AVG() it's better style to list out the columns you are selecting instead of using *, and GROUP BY all of them.

SELECT auto.a_id, auto.a_type, avg(rating.score) as score, gebruikers.g_id
FROM auto
LEFT JOIN gebruikers ON auto.g_id = gebruikers.g_id
LEFT JOIN rating ON auto.a_id = rating.a_id
GROUP BY auto.a_id, auto.a_type, gebruikers.g_id
ORDER BY auto.a_type

Hello Tom,

Thank you soooo much for helping me, you hit the nail right on his head and
both of my problems were solved with your query. By looking at the query alone
i have learned more of mysql already! and it works like a charm!

Honestly, i cant express myself enough of how happy i am with your reply!
and you did it so quick after my topic start 🙂

Greets, Xafro

Edit: P.S: Is there any way i can put the [Resolved] in front of the subject? is there any way of giving you forum points or reputation points as commonly seen on other boards? Thanks again 😉

[Weedpacket]

Xafro wrote:

Edit: P.S: Is there any way i can put the [Resolved] in front of the subject?

It's in one of the menu bars near the top of the page, under "Thread Tools".

[Xafro]

Weedpacket;10897894 wrote:

It's in one of the menu bars near the top of the page, under "Thread Tools".

Done, thanks 🙂