[SQL] Sorting..

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
andrej22
Forum Newbie
Posts: 2
Joined: Sat Jun 21, 2008 7:36 pm

[SQL] Sorting..

Post 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 ;)
Attachments
bart_enoclegi.zip
(847 Bytes) Downloaded 119 times
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: [SQL] Sorting..

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: [SQL] Sorting..

Post 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.
andrej22
Forum Newbie
Posts: 2
Joined: Sat Jun 21, 2008 7:36 pm

Re: [SQL] Sorting..

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: [SQL] Sorting..

Post by Kieran Huggins »

@Weirdan: you're on fiiiiiire!
Post Reply