mysql joins

Code junkies hangout here

Moderators: ChrisThornett, LXF moderators

mysql joins

Postby bobthebob1234 » Wed Jan 04, 2012 11:30 am

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 all
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
User avatar
bobthebob1234
LXF regular
 
Posts: 1373
Joined: Thu Jan 03, 2008 9:38 pm
Location: A hole in a field

Postby greg.d » Wed Jan 04, 2012 5:00 pm

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.
greg.d
 
Posts: 24
Joined: Thu Oct 27, 2005 8:29 am

Postby bobthebob1234 » Sun Jan 08, 2012 10:55 pm

Well its more the query that is bothering me.

Code: Select all
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
User avatar
bobthebob1234
LXF regular
 
Posts: 1373
Joined: Thu Jan 03, 2008 9:38 pm
Location: A hole in a field

Postby CJLL » Sat Jan 14, 2012 6:32 am

bobthebob1234 wrote:Well its more the query that is bothering me.

Code: Select all
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 all
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 all
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
CJLL
LXF regular
 
Posts: 193
Joined: Sat Jul 09, 2005 9:22 pm

Re: mysql joins

Postby maverickprowls » Sat Nov 10, 2012 1:14 pm

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 all
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.
maverickprowls
 
Posts: 3
Joined: Thu Jan 11, 2007 7:07 pm


Return to Programming

Who is online

Users browsing this forum: No registered users and 3 guests