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
Who is Participating?
chaauConnect With a Mentor Commented:
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
SELECT id, biz_name, biz_address, biz_phone, admin _notes, 2 AS sort_id FROM imported_listing
SELECT id, biz_name, biz_address, biz_phone, admin _notes, 3 AS sort_id FROM usa_biz_listing

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
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
Ray PaseurConnect With a Mentor Commented:
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!
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.
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.

All Courses

From novice to tech pro — start learning today.