how to join mysql tables

i have 3 tables id like to join, and understand how to get info out of them.
lets say for purpose of this question:

{id like to use mysqli and php please}

all 3 have the same fields.

id, biz_name, biz_address, biz_phone, admin _notes

table 1 is paid_listing
table 2 is imported_listing
table 3 is usa_biz_listing

i do not want to have them all in the same table, as i will be phasing out data in the long run. i also have other reasons too.
i need the tables to be in priority too so if a listing is the same in all 3 tables paid takes precedence over imported and imported takes precedence over usa_biz

how does one do this please, can you explain how it is done too as i do not understand the join tables looking at places like join syntax

Thank you in advance for any code or help you may provide
Johnny
JohnnyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Usually you do this using UNION clause, not JOIN. In order to sort them correctly you need to introduce a sort columns, like this:

SELECT id, biz_name, biz_address, biz_phone, admin _notes, 1 AS sort_id FROM paid_listing
UNION ALL
SELECT id, biz_name, biz_address, biz_phone, admin _notes, 2 AS sort_id FROM imported_listing
UNION ALL
SELECT id, biz_name, biz_address, biz_phone, admin _notes, 3 AS sort_id FROM usa_biz_listing
ORDER BY 6

Open in new window


Have you noticed here how I have ordered the results by a column #6. This is your sort order.

You may want to sort by biz_name first, and then by sort_id. This al depends on the reporting requirements you have
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnnyAuthor Commented:
what is 1,2,3 number represent plz.
and the sort_id is what?

order by 6 is sorting on sort_id?

i actually need to sort on city,state (not in the field listing, forgot that part) and on category,tag (again not in field list)

sorry about not thinking ahead.

thank you for info so far, and why i couldn't get it with join
0
Ray PaseurCommented:
If all of the tables have the same columns and the only difference is the kind of listing, I think it would make a lot of sense to add a "kind of listing" column and merge the tables.  In a flash your queries will be come simpler and more powerful!
0
JohnnyAuthor Commented:
@chaau thanks you answered my question.

@Ray_Paseur i went with this as it easyer and i don't need to make multi tables this way.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.