Solved

How to return data from 3 related tables?

Posted on 2010-09-10
18
381 Views
Last Modified: 2013-12-13
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
Comment
Question by:colonelblue
  • 7
  • 6
  • 2
  • +3
18 Comments
 
LVL 3

Expert Comment

by:59cal
ID: 33650412
Hi,

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

Expert Comment

by:z_alex
ID: 33650611
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
 
LVL 11

Expert Comment

by:aelliso3
ID: 33650622
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
 

Author Comment

by:colonelblue
ID: 33650707
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
 
LVL 11

Expert Comment

by:aelliso3
ID: 33650720
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
 
LVL 4

Expert Comment

by:yesthatbob
ID: 33650748
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
 
LVL 11

Expert Comment

by:aelliso3
ID: 33650931
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
 
LVL 4

Expert Comment

by:yesthatbob
ID: 33651017
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33651559
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:colonelblue
ID: 33651731
OMG I'm sorry I meant this for MYSQL.
My apologies and thanks to all that replied.
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33651875
We're all allowed ... any luck yet with the results yet?
0
 

Author Comment

by:colonelblue
ID: 33652296
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
 
LVL 11

Expert Comment

by:aelliso3
ID: 33654602
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
 
LVL 11

Accepted Solution

by:
aelliso3 earned 500 total points
ID: 33654606
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
 

Author Comment

by:colonelblue
ID: 33655777
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
 

Author Closing Comment

by:colonelblue
ID: 33655782
aelliso3 , you're awesome.
Thank you for being such a kind soul.
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33656523
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
 

Author Comment

by:colonelblue
ID: 33657185
Thank you aelliso3. I understand now.
Best wishes.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
The viewer will learn how to count occurrences of each item in an array.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now