mysql query to select data from three tables

Question

wall_paths (table)

wall_paths

wall_dimensions

enter image description here

dimensions (reference table)

enter image description here

What I've got so far:

Query

SELECT wall_paths.wall_id, wall_paths.wall_path, 
       dimensions.width, dimensions.height
FROM wall_paths
LEFT JOIN wall_dimensions
ON wall_paths.wall_id = wall_dimensions.wall_id
LEFT JOIN dimensions
ON wall_dimensions.dimension_id = dimensions.dimension_id
WHERE wall_paths.wall_id = 4;

Result

enter image description here

The query is selecting redundant rows, I just would like to select the two paths along with the corresponding dimensions based on the wall_dimensions table. Something like below:

Expected result

enter image description here

Result with GROUP BY in query

enter image description here

Please help on how to select something like the above.

Note: I've tried DISTINCT as well on the query but returns an error.

Hmmm...is there a difference if you INNER JOIN the tables? I wonder if the LEFT JOINs are somehow creating a CROSS JOIN-like product... — VBlades
– VBlades, Commented Jun 22, 2014 at 11:12
@VBlades, same result with INNER JOIN as well. I guess there is no major difference between the two. — vephelp
– vephelp, Commented Jun 22, 2014 at 11:15
It isn't clear how you link path_id = 7 with dimension_id = 4 and path_id = 8 with dimension_id = 5?? Given wall_paths is linked to wall_dimension by wall_id, each of those 2 rows in wall_paths are linked to both rows in wall_dimension. So those 4 rows is exactly what we expect your query to do. — har07
– har07, Commented Jun 22, 2014 at 11:35
I don't understand why you consider one to be redundant. You have two dimension ids, so two results !?! — Strawberry
– Strawberry, Commented Jun 22, 2014 at 11:42

VBlades · Accepted Answer · 2014-06-22 11:38:20Z

2

The query results are as expected, actually. Makes sense when you see it, data-wise. The data model is setup incorrectly for this query.

Fiddle (original data model): http://sqlfiddle.com/#!2/8c1bf/1

Fiddle (modified data model): http://sqlfiddle.com/#!2/3d9b0/5

Not saying you should change your model, just pointing out the query runs as expected with the current schema.

answered Jun 22, 2014 at 11:38

VBlades

2,2511 gold badge14 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

vephelp Over a year ago

just a question, wouldn't be better if I insert the dimension_id in the wall_paths table than inserting the path_id to the dimensions table?

VBlades Over a year ago

@vephelp: Yes, that would absolutely make more sense. The dimension is an attribute of the wall_paths object, not vice versa. Your goal with the dimensions table is to normalize the dimensions data; if you put path_id in there, it would defeat the purpose. And then drop the wall_dimensions table as it's just redundant at that point (looks like it always was, tbh - there isn't any unique data in there). Fiddle: sqlfiddle.com/#!2/854344/1

VBlades Over a year ago

@vephelp: Just to put it out there, I think this is where the original idea of the data model was headed: sqlfiddle.com/#!2/e162c/6.

vephelp Over a year ago

alright! I've done that buddy! Added dimension_id to wall_paths table. Thanks a lot!

Collectives™ on Stack Overflow

mysql query to select data from three tables

wall_paths (table)

wall_dimensions

dimensions (reference table)

Query

Result

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

wall_paths (table)

wall_dimensions

dimensions (reference table)

Query

Result

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related