Combining 3 tables in MySQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining 3 tables in MySQL

Author

Topic

jdsmith8
Starting Member

11 Posts

Posted - 2014-07-02 : 15:50:40

Hi All,

I have a quick question for the SQL community about how to combine 3 tables to get the results needed...

The table names are :
setup_zipcode,
setup_category,
and listing

and inside setup_zipcode it has the columns:
zip_id , zip_code, zip_latitude and zip_longitude

and inside the setup_category it has the columns:
category_id, category_parent, category_path and category_name

and the final table for listing has the columns:
listing_id, listing_member, listing_category, listing_address, listing_city, listing_state, listing_zip and listing_country

I am having trouble getting them to inter-relate an query the needed results as I need to get back the LAT & LONG from the zip_latitude & zip_longitude columns for specific listing ids in certain categories...

So, the ONLY same variable between them is that listing_zip from LISTING table and zip_code from SETUP_ZIPCODE show the same zip codes..

How can I create a SQL query that checks the current category that is being displayed on the page results live and insert only the listing id (clients) that are in that listing_category and also pull that listing_id client's related zip_latitude & zip_longitude that relates to their specific listing_zip from the zip_code row in setup_zipcode?

I have tried many things and this looks like it would work but does not pull the related LAT & LONGs ...

$cat is assigned in a query previously on the page...

SELECT *
FROM
setup_zipcode, setup_category, listing
WHERE
listing_category LIKE '%-$cat-%'
AND
listing_zip = zip_code
AND
category_id = '$cat'
ORDER BY listing_title ASC

When I try to take the results (not sure if I am missing a step for printing the results after querying them or having to assign them somehow) and use the SMARTY TAGS assigned to zip_latitude and zip_longitude nothing shows on the published page... The other variables for the address do..

I have a loop defined as $listing_id and section is var so when I pull the query info using {$listing_address[var]}, {$listing_city[var]}, etc.. they work, but {$zip_latitude[var]} or {$listing_id[var].$zip_latitude} and {$listing_id[var].zip_latitude} so on do not work..

If I can get the variables to exist in the SQL QUERY then at least I will know that is correct and can work on how to get them to show correctly in the address for a map afterwards...

Thanks in advance,
Jay

James Smith

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-02 : 16:24:33

SQLTeam.com is for Microsoft SQL Server. Though we can likely help, our answers will be in T-SQL and not the language that MySQL uses. If you'd like help in T-SQL, please follow this link that'll show you how to post the needed information for us to help: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources