CODEWITHSUNDEEP
mysqlsqljoin
HapiDjus
HapiDjus

Reputation: 1

Combine three tables into one when viewing

I have three tables as seen in this image and I want to present it as seen in the last table. I can't figure out how to solve it - right now I'm using three nestled calls to display it.

First I loop through Customer to display all of them. Inside this loop I have a loop that goes through OrderCustom and inside that I check if there is a CustomerOrderCustom with the right Customer_id and OrderCustom_id.

Not only am I using a lot of queries but the view shows OrderCustom items that now Customer are using, in this case Zip Code. I'm using MySQL 5.

schema

Upvotes: 0

Views: 129

Answers (1)

Larry Lustig
Larry Lustig

Reputation: 51000

This is an entity-attribute-value database design. It is not relational in design and you will not be able to manipulate it with relational operations (such as JOINs) except for the most trivial examples.

If you are determined to store this non-relational data in a relational database you'll be dependent on either your own code or some EAV-based object serialization and deserialization library for whatever programming language you're using. SQL will be of little use to you.

If you are really required to use a model like this for this project (that is, you cannot adopt a relational model) then, if it is not too late in the development process, I would suggest abandoning SQL and reading up on XML, XPath, and XSLT which are probably a better fit for storing and recovering data in which each entry can have a different structure.

Bonus Article: "Why Entity-Attribute-Value is bad"

Upvotes: 2

Related Questions