How to return data from 3 related tables?

Hi,

you need to sub select a calculated field as a column to give you the results in the same row.

SELECT locations.ID, locations.name, locations_field_link.location_fields_id, locations_field_link.location_id, locations_field_link.value, locations_fields.name, locations_fields.type
FROM locations_fields INNER JOIN (locations INNER JOIN locations_field_link ON locations.ID = locations_field_link.location_id) ON locations_fields.ID = locations_field_link.location_fields_id;

colonelblue,
The design that you are working with is called a EAV schema (Entity Attribute Value). Very efficient by design, but not all that easy for a new SQL person to get use to. The query below is what is needed to pull the data.
 
Feel free to ask any questions needed to help understand what's going on with it.
 
**Note that the locations_fields is not needed in order to return the results.

SELECT l.Name
	, Address.value AS Address
	, Address2.value AS Address2
	, City.value AS City
	, State.value AS State
	, PostalCode.value AS PostalCode
	, Phone.value AS Phone
	, Date.value AS Date
	, Country.value AS Country
FROM locations l
	LEFT JOIN locations_fields_link [Address]
		ON Address.location_id = l.id
		AND Address.location_fields_id = 1
	LEFT JOIN locations_fields_link [Address2]
		ON Address2.location_id = l.id
		AND Address2.location_fields_id = 2
	LEFT JOIN locations_fields_link [City]
		ON City.location_id = l.id
		AND City.location_fields_id = 3
	LEFT JOIN locations_fields_link [State]
		ON State.location_id = l.id
		AND State.location_fields_id = 4
	LEFT JOIN locations_fields_link [PostalCode]
		ON PostalCode.location_id = l.id
		AND PostalCode.location_fields_id = 5
	LEFT JOIN locations_fields_link [Phone]
		ON Phone.location_id = l.id
		AND Phone.location_fields_id = 6
	LEFT JOIN locations_fields_link [Date]
		ON Date.location_id = l.id
		AND Date.location_fields_id = 7
	LEFT JOIN locations_fields_link [Country]
		ON Country.location_id = l.id
		AND Country.location_fields_id = 8

Select allOpen in new window

Gee thank you aelliso3. VERY gracious of you!
Wow that looks complex!

As for that EAV, unfortunately I did not make that scheme, nor cam I change it.
I must be doing something wrong, I got this error on the included image.


error1.png

Ah ... it's MySQL ... what I have up there is MS SQL.
 
I'm not completely sure of all the syntax for MySQL, but one thing I would try is removing all the  [    and    ]    from the joins. I do not know if MySQL allows those.
 
Ie ...[Address]    should just be     Address

As elegant as it is, the problem with aelliso3's solution is that the query defeats the original design of the tables. It seems the locations_fields table is there to allow users to easily add fields to address data. By hard coding each field's name as as columns returned in the table, this query has to change every time a user adds new rows to the locations_fields table.
I would question why the address fields have to be entered as rows in the fields table. Do you really anticipate the rows in location_fields table to grow significantly? Why not simply have the locations table include columns such as Address, City, State, etc.? Doing so would simplify queries, reduce joins (improving performance), and generally make the program, much more maintainable.

yesthatbob ... although I agree in this particular situation ... in his defense, he did say its not his design and not in his control to change. I like EAV when used properly, but this situation does not seem to warrant the value

aelliso3,
Yeah, I saw that after I opened my big fingers.
In this case, if it were actually SQL Server, I would suggest dynamically building and executing your query from the entered data in a stored proc. This would allow the underlying data to control what the query looks like. I'm sure MySQL has some stored proc facility, but I wouldn't be able to convert my idea into a solution for that db.

You may want to get an EE Moderator to move this question to a more appropriate zone, such as:
https://www.experts-exchange.com/Database/MySQL/

OMG I'm sorry I meant this for MYSQL.
My apologies and thanks to all that replied.

We're all allowed ... any luck yet with the results yet?

Thank you aelliso3.
Sorry for the late reply. I had just got home.

I actually did the happy dance!!
YOU ARE A DEITY.
I placed this in the wrong forum and yet the man gets it right. Thank you!

One last thing If I may?
I forgot an integral table that was part of the others.

How would I add a table called
location_zips
with the field names:
zip
state
location_id ( relative key )
country
station
quadrant

This table has a few field names that are of the same name as another table how does that work?

Seeing how you add the above to what you generously provided is indeed teaching me for which I am so genuinely grateful.

I added it below with a LEFT JOIN and 2 fields that are not  in the other tables.

SELECT l.Name
	, Address.value AS Address
	, Address2.value AS Address2
	, City.value AS City
	, State.value AS State
	, PostalCode.value AS PostalCode
	, Phone.value AS Phone
	, Date.value AS Date
	, Country.value AS Country
	, lc.station
	, lc.quadrant
FROM locations l
	LEFT JOIN location_zips lc
		ON l.location_id - lc.location_id
	LEFT JOIN locations_fields_link [Address]
		ON Address.location_id = l.id
		AND Address.location_fields_id = 1
	LEFT JOIN locations_fields_link [Address2]
		ON Address2.location_id = l.id
		AND Address2.location_fields_id = 2
	LEFT JOIN locations_fields_link [City]
		ON City.location_id = l.id
		AND City.location_fields_id = 3
	LEFT JOIN locations_fields_link [State]
		ON State.location_id = l.id
		AND State.location_fields_id = 4
	LEFT JOIN locations_fields_link [PostalCode]
		ON PostalCode.location_id = l.id
		AND PostalCode.location_fields_id = 5
	LEFT JOIN locations_fields_link [Phone]
		ON Phone.location_id = l.id
		AND Phone.location_fields_id = 6
	LEFT JOIN locations_fields_link [Date]
		ON Date.location_id = l.id
		AND Date.location_fields_id = 7
	LEFT JOIN locations_fields_link [Country]
		ON Country.location_id = l.id
		AND Country.location_fields_id = 8

Select allOpen in new window

Thank you so much aelliso3!!  I am so thankful.

Just would like to learn and solve some mystery to the code.

What does the "l" stand for in these lines below. I'd assume it has something to do with the ledt join. I searched google and could not find a clue on what the "l" stands for in function.

SELECT l.Name

FROM locations l

      
And for the names chosen such as
, lc.station
, lc.quadrant

was "lc" arbitrary naming or mandatory.

Thanks again and promise not to bug ya with any more questions. :)

aelliso3 , you're awesome.
Thank you for being such a kind soul.

The l is just an Alias. When you use an alias, you are just making the code more readable. All it is doing is renaming the table to something else
              SELECTl.Name
              FROM locations l
is the same as
              SELECT locations.Name
              FROM locations
you'll see the same thing in all the joins that are being used for the derived columns:
               LEFT JOIN locations_fields_link        Address
                         ON Address.location_id = l.id
                         AND Address.location_fields_id = 1
 
the above is one of many examples that are using the location_fields_id to determine which values you are wanting. 'Address' is the alias and could have been named anything. The code below would do the eact same thing
               LEFT JOIN locations_fields_link        Ad
                         ON Ad.location_id = l.id
                         AND Ad.location_fields_id = 1

Thank you aelliso3. I understand now.
Best wishes.