MYSQL Single query to retrieve both single row from one table and many rows as a single field from another

chip

I suspect from how difficult it was to come up with a title for this question that what I am wondering is possible, is simply not. But I am by no means a mysql guru, so here goes.

I am currently rewriting an application I was tasked to maintain. It's essentially an event management system that books rooms. A single event will often have multiple rooms associated with it.

It's quite common throughout the application in different parts to have to list a table of events that include the rooms in a column along with other event information.

At present the application stores the rooms as a comma separated list of id numbers, eg. 1,5,7 I want to normalise this to help with other queries that are currently difficult due to this arrangement.

So I have separated the rooms from the events table with another table called event_rooms. This table consists of

id
event_id
room_id

And I'm putting in multiple rows into this table for each room an event is in. This fixes lots of problems in the application and ideally I'd like to keep it like this.

So getting to the question I am trying to work out if with a single query I can get the event table row along with all the rooms the event is in ? To allow me to create tables of events simply that include the list of events.

The event table is simple, basically

-id
-event_name
-event_date
-notes

Is this possible ? Or do I have to do multiple queries (with 100+ events on a page I am keen to avoid this since this will mean 101 queries+ per page.

Or is there a overall better design choice I may not have considered ? I'm working with PHP in case your ideas involve application code.

Zen_silence

I made a SQL Fiddle which I think solves what you were asking: http://sqlfiddle.com/#!2/71c8d/4

There are two queries:

  • the first one returns a comma separated list of the room id's
  • the second one returns a comma separated list of the room numbers because i assume you have a rooms table as well

But as Strawberry commented this is something that would be covered on any basic mysql tutorial I reccomend http://www.w3schools.com/sql/ as a good starting point.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

MySQL query - single row in one table with multiple rows in another table

From Dev

Insert multiple rows with single a query from one table into another in Oracle

From Dev

MySQL: combine 2 rows from another table into a single result row

From Dev

Postgres select from one to many table to single table rows

From Dev

Sqlalchemy single query for multiple rows from one column in one table

From Dev

SQL insert multiple rows from single field in another table

From Dev

Count number of rows from another table in single query

From Dev

MySQL / PHP - Display all rows from table except one single row

From Dev

MySQL List some field from a single table and COUNT all particular field reference from another table

From Dev

MYSQL combine multiple rows into single row using join with another table

From Dev

One to many on single row

From Dev

How to retrieve a single field from a table in laravel 5.2

From Dev

mysql query to retrieve every single row of data

From Dev

JOIN query to retrieve multiple column referencing to a single column from another table

From Dev

Selecting SUM+COUNT from one table and COUNT from another in Single query

From Dev

join single value from one table to multiple rows table - Oracle

From Dev

Insert multiple rows from a single row inside an insert into select query

From Dev

How to get 1 to 20 row from 5000 rows in a single query

From Dev

Concatenate field from multiple rows into single field

From Dev

Mysql query to retrieve single image from an array of images?

From Dev

How to select data from several rows in a single table in one result row?

From Dev

How to combine multiple rows from 4 tables into one single row in a new table in SQL?

From Dev

How to retrieve a single field from MongoDB in Java

From Dev

How to select a single row where multiple rows exist from a table

From Dev

How to select a single row where multiple rows exist from a table

From Dev

Fetch single row from two rows of a table with join in SQL Server

From Dev

How to get multiple rows from single row of table?

From Dev

insert data from one table to a single column another table with no relation

From Dev

Returning a single record from many to many table

Related Related

  1. 1

    MySQL query - single row in one table with multiple rows in another table

  2. 2

    Insert multiple rows with single a query from one table into another in Oracle

  3. 3

    MySQL: combine 2 rows from another table into a single result row

  4. 4

    Postgres select from one to many table to single table rows

  5. 5

    Sqlalchemy single query for multiple rows from one column in one table

  6. 6

    SQL insert multiple rows from single field in another table

  7. 7

    Count number of rows from another table in single query

  8. 8

    MySQL / PHP - Display all rows from table except one single row

  9. 9

    MySQL List some field from a single table and COUNT all particular field reference from another table

  10. 10

    MYSQL combine multiple rows into single row using join with another table

  11. 11

    One to many on single row

  12. 12

    How to retrieve a single field from a table in laravel 5.2

  13. 13

    mysql query to retrieve every single row of data

  14. 14

    JOIN query to retrieve multiple column referencing to a single column from another table

  15. 15

    Selecting SUM+COUNT from one table and COUNT from another in Single query

  16. 16

    join single value from one table to multiple rows table - Oracle

  17. 17

    Insert multiple rows from a single row inside an insert into select query

  18. 18

    How to get 1 to 20 row from 5000 rows in a single query

  19. 19

    Concatenate field from multiple rows into single field

  20. 20

    Mysql query to retrieve single image from an array of images?

  21. 21

    How to select data from several rows in a single table in one result row?

  22. 22

    How to combine multiple rows from 4 tables into one single row in a new table in SQL?

  23. 23

    How to retrieve a single field from MongoDB in Java

  24. 24

    How to select a single row where multiple rows exist from a table

  25. 25

    How to select a single row where multiple rows exist from a table

  26. 26

    Fetch single row from two rows of a table with join in SQL Server

  27. 27

    How to get multiple rows from single row of table?

  28. 28

    insert data from one table to a single column another table with no relation

  29. 29

    Returning a single record from many to many table

HotTag

Archive