Linux Format forums Forum Index Linux Format forums
Help, discussion, magazine feedback and more
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

mysql joins

 
Post new topic   Reply to topic    Linux Format forums Forum Index -> Programming
View previous topic :: View next topic  
Author Message
bobthebob1234
LXF regular


Joined: Thu Jan 03, 2008 9:38 pm
Posts: 1360
Location: A hole in a field
PostPosted: Wed Jan 04, 2012 11:30 am    Post subject: mysql joins Reply with quote
I've not used joins before, but think they would be idea for what I want to do here.

I have tables events & dates.
events has event_id as its primary key
dates has event_id as a foreign key, and also date

an event can have many dates

I want to select all the events (only once) and order them by the (most recent) date

I have
Code:

SELECT * FROM events INNER JOIN dates ON events.event_id = dates.event_id GROUP BY `event_name` ORDER BY `date` DESC


which appears to work but isn't very pretty. Is there a better way?

Thanks
_________________
For certain you have to be lost to find the places that can't be found. Elseways, everyone would know where it was
Back to top
View user's profile Send private message
greg.d



Joined: Thu Oct 27, 2005 9:29 am
Posts: 23
PostPosted: Wed Jan 04, 2012 5:00 pm    Post subject: Reply with quote
Is it the output you dont like or the query?

From what I can gather, because your 2 columns have the same name, you can use the USING clause instead of ON. Ie. USING(event_id) or something like that.

Other than that it looks ok to me, but I'm no expert.
Back to top
View user's profile Send private message
bobthebob1234
LXF regular


Joined: Thu Jan 03, 2008 9:38 pm
Posts: 1360
Location: A hole in a field
PostPosted: Sun Jan 08, 2012 10:55 pm    Post subject: Reply with quote
Well its more the query that is bothering me.

Code:

SELECT * FROM events, dates WHERE events.event_id = dates.event_id GROUP BY `event_name` ORDER BY `date` DESC


does the same thing, so I feel I may be missing the point of joins...
_________________
For certain you have to be lost to find the places that can't be found. Elseways, everyone would know where it was
Back to top
View user's profile Send private message
CJLL
LXF regular


Joined: Sat Jul 09, 2005 10:22 pm
Posts: 193
PostPosted: Sat Jan 14, 2012 6:32 am    Post subject: Reply with quote
bobthebob1234 wrote:
Well its more the query that is bothering me.

Code:

SELECT * FROM events, dates WHERE events.event_id = dates.event_id GROUP BY `event_name` ORDER BY `date` DESC


does the same thing, so I feel I may be missing the point of joins...


Straight joins between multiple tables can easily be performed in the WHERE section.

However in your application you will eventually need to use left or right joins, where one table may be populated and the other isn't.

For example, supposing you want to list ALL events, with dates where appropriate.

Using your example, you would only list events that had dates but not the events which yet had to have dates created.

Therefore you need a different type of join, which will list all events and any dates attaching to those events.

The solution to this is a LEFT JOIN.

Code:

SELECT * FROM events LEFT JOIN dates ON events.event_id = dates.event_id GROUP BY `event_name` ORDER BY `event_name` DESC


To take this even further suppose you need to know which events do not have any dates, by checking to see if the date record is populated, you can easily find the answer.

Code:

SELECT * FROM events LEFT JOIN dates ON events.event_id = dates.event_id WHERE dates.event_id IS NULL GROUP BY `event_name` ORDER BY `event_name` DESC


*disclaimer* it's 5am and I've not tested the above queries
_________________
--
The reward for self love is sticky hands
Back to top
View user's profile Send private message
maverickprowls



Joined: Thu Jan 11, 2007 7:07 pm
Posts: 3
PostPosted: Sat Nov 10, 2012 1:14 pm    Post subject: Re: mysql joins Reply with quote
bobthebob1234 wrote:

I want to select all the events (only once) and order them by the (most recent) date


You say that you want to select your events only once, but this query will join each time there is a date for an event, so you'll get several rows returned for any event with more than one date.

For a single row for each event showing the most recent event you'd need something like this (note: not tested, and my experience is SQL Server so you might need to check the syntax for MySQL)

Code:

SELECT e.event_id, d.maxdate FROM events AS e
INNER JOIN
  (SELECT event_id, MAX(date) AS maxdate FROM dates GROUP BY event_id) AS d ON d.event_id = e.event_id
ORDER BY d.maxdate DESC


This joins your events to only the most recent (max) date in your dates table.

Additionally, it's good practice to expand * to the fields you want to return, that way your queries don't (necessarily) break when you change your tables.
Back to top
View user's profile Send private message
View previous topic :: View next topic  
Display posts from previous:   
Post new topic   Reply to topic    Linux Format forums Forum Index -> Programming All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Linux Format forums topic RSS feed 


Powered by phpBB © 2001, 2005 phpBB Group


Copyright 2011 Future Publishing, all rights reserved.


Web hosting by UKFast