0

My goal is as follows (simplified):

  1. find the newest record from table events
  2. use a FK events_meta_id to find all records from that same table (events) that have the same value in that field, if they exist

But, I'd like the subsequent records to appear as different rows, not as a part of the same row (like it does with INNER JOIN)

Schemas

CREATE TABLE `events` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `events_meta_id` int(11) unsigned NOT NULL,
  `tickets_quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `events_meta_id` (`events_meta_id`),
  CONSTRAINT `events_ibfk_1` FOREIGN KEY (`events_meta_id`) REFERENCES `events` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

CREATE TABLE `events_meta` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `artist` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

My query is as follows:

SELECT * FROM
(
    SELECT * FROM events
    ORDER BY id DESC
    LIMIT 1
) latest_events
INNER JOIN events
ON latest_events.events_meta_id = events.events_meta_id

events_meta contents

| id |   artist   |
|----|------------|
|  1 | Joe Cocker |
|  2 | BB King    |
|  3 | Nirvana    |

events contents

  id | events_meta_id | tickets_quantity 
 ----|----------------|------------------ 
   1 |              1 |               10 
   2 |              2 |               20 
   3 |              3 |               30 
   4 |              1 |                5 

The resultset right now is this:

| id | events_meta_id | tickets_quantity | id | events_meta_id | tickets_quantity |
|----|----------------|------------------|----|----------------|------------------|
|  4 |              1 |                5 |  1 |              1 |               10 |
|  4 |              1 |                5 |  4 |              1 |                5 |

But I want this:

| id | events_meta_id | tickets_quantity |
|----|----------------|------------------|
|  4 |              1 |                5 |
|  1 |              1 |               10 |

Any ideas how?

  • 1
    Replace SELECT * FROM with SELECT events.* FROM – Akina Jul 19 '19 at 5:41
  • @Akina that fixed it, thanks a bunch (so simple) – Nino Škopac Jul 19 '19 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.