Linux Format forums

Re: mysql joins

maverickprowls — Sat Nov 10, 2012 1:14 pm

Author: maverickprowls

Posted: 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 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.

Re: mysql joins

CJLL — Sat Jan 14, 2012 6:32 am

Author: CJLL

Posted: Sat Jan 14, 2012 6:32 am

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

Re: mysql joins

bobthebob1234 — Sun Jan 08, 2012 10:55 pm

Author: bobthebob1234

Posted: Sun Jan 08, 2012 10:55 pm

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...

Re: mysql joins

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

Author: greg.d

Posted: 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.

mysql joins

bobthebob1234 — Wed Jan 04, 2012 11:30 am

Author: bobthebob1234

Posted: 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 * 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