Page 1 of 1

[SQL] Sorting..

Posted: Sat Jun 21, 2008 8:09 pm
by andrej22
Welcome ;)

I have query:

Code: Select all

 
SELECT
m.id,
n.nazwa miejscowosc,
o.id,
o.nazwa
FROM miejscowosci m LEFT JOIN miejscowosci_nazwa n ON n.m_id = m.id AND n.j_id = '1' LEFT JOIN obiekty o ON o.miejscowosc = m.id
GROUP BY m.id;
 
I would like get all unique records from table 'miejscowosci' and one, random record from table 'obiekty'. Now I have always first record from 'obiekty' where field miejscowosc = miejscowosci.id (it's ok!)

I was traying also somethink like this:

Code: Select all

 
SELECT m.id, n.nazwa miejscowosc, o.id, o.nazwa
FROM miejscowosci m
LEFT JOIN miejscowosci_nazwa n ON n.m_id = m.id AND n.j_id = '1'
LEFT JOIN obiekty o ON o.miejscowosc = m.id AND o.id = (
SELECT id
FROM obiekty WHERE miejscowosc = m.id
ORDER BY RAND( )
LIMIT 1 )
GROUP BY m.id;
 
now sometimes it's work ok, but sometimes I have only m.id, n.nazwa and o.id and o.nazwa is null... I don't know whay.

... and ...

Code: Select all

 
SELECT
n.nazwa,
m.id,
o.id,
o.nazwa
FROM miejscowosci m LEFT JOIN miejscowosci_nazwa n ON n.m_id = m.id AND n.j_id = '1', obiekty o
WHERE o.id = (SELECT id FROM obiekty WHERE miejscowosc = m.id ORDER BY RAND() LIMIT 1 )
GROUP BY
m.id
 
now sometimes all is but and sometimes i don't have same records from 'miejscowosci'...

I attach my DB schema and example values.

Sorry for my very bad english and thanks ;)

Re: [SQL] Sorting..

Posted: Sun Jun 22, 2008 2:46 am
by Kieran Huggins
I'm a little confused by what you're trying to do exactly.

Is there a reason you're not selecting all the records in one query, and a random record in another?

It might help if we understood your goal.

Re: [SQL] Sorting..

Posted: Sun Jun 22, 2008 3:49 am
by Weirdan
Are you trying to select all locations with a random object attached to every selected location (where object must belong to the location)?

BTW, it would be beneficial in the long term to switch names in your db schema to English. While I barely understand Polish (that's Polish, right?) others may not, and thus you're limiting maintainability of your project.

Re: [SQL] Sorting..

Posted: Sun Jun 22, 2008 4:20 am
by andrej22
Are you trying to select all locations with a random object attached to every selected location (where object must belong to the location)?
Yes, exactly :)
BTW, it would be beneficial in the long term to switch names in your db schema to English. While I barely understand Polish (that's Polish, right?) others may not, and thus you're limiting maintainability of your project.
Yes, it's polish. I'll change names and edit post.

Re: [SQL] Sorting..

Posted: Sun Jun 22, 2008 7:11 am
by Kieran Huggins
@Weirdan: you're on fiiiiiire!