• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

How to return data from 3 related tables?

Hello again experts.
I know enough to write a simple query from one table but despite the fact that I understand how these 3 tables are related I do not know how to query them to display the needed info the way I like.
The problem is the lookup table has IDs and values for each part of an address and when I query it with my statement each part of that lookup table comes up as a new row and not a column. ( please see attached pic ).
I really am trying as hard as I can to understand this.
Could an expert please direct me the right way?
My snapshots and scribblings should help explain what I am trying to do.
Thank you in advance.


SELECT *
FROM jml_locations, jml_location_fields_link, jml_location_fields
WHERE jml_locations.id=jml_location_fields_link.location_id AND jml_location_fields.id =  jml_location_fields_link.location_fields_id

Open in new window

EE.png
EE3.png
0
colonelblue
Asked:
colonelblue
  • 7
  • 6
  • 2
  • +3
1 Solution
 
59calCommented:
Hi,

you need to sub select a calculated field as a column to give you the results in the same row.
0
 
z_alexCommented:
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;
0
 
aelliso3Commented:
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

Open in new window

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
colonelblueAuthor Commented:
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
0
 
aelliso3Commented:
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
0
 
yesthatbobCommented:
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.
0
 
aelliso3Commented:
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
0
 
yesthatbobCommented:
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.
0
 
Anthony PerkinsCommented:
You may want to get an EE Moderator to move this question to a more appropriate zone, such as:
http://www.experts-exchange.com/Database/MySQL/
0
 
colonelblueAuthor Commented:
OMG I'm sorry I meant this for MYSQL.
My apologies and thanks to all that replied.
0
 
aelliso3Commented:
We're all allowed ... any luck yet with the results yet?
0
 
colonelblueAuthor Commented:
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.








0
 
aelliso3Commented:
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

Open in new window

0
 
aelliso3Commented:
Oops ... apparently I don't know the difference between a   -   and a     =
The one below is corrected

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

Open in new window

0
 
colonelblueAuthor Commented:
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. :)
0
 
colonelblueAuthor Commented:
aelliso3 , you're awesome.
Thank you for being such a kind soul.
0
 
aelliso3Commented:
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
0
 
colonelblueAuthor Commented:
Thank you aelliso3. I understand now.
Best wishes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now