Joining 2 tables in SELECT(MYSQL/PHP)

I have these two tables. The connecting “key” is conID which is included in both tables.
So now i would like to write select statement which would give me something like this:

John Smith dairy22 Texas 4000 smth4

Mike Situation glenn32 Jersey 1000 smth1
…

> table “people”:

NUM   Name lastName  address    conID
-----------------------------------------
  1    John  Smith     dairy22   Texas
  2    Mike  Situation glenn32   Jersey
  3    Duke  Nukem     haris48   NYork
  4    Queen Lisa      court84   London

> table “countries”

conID   postNum   region
-------------------------
Jersey  1000      smth1
NYork   2000      smth2
London  3000      smth3
Texas   4000      smth4

! -> NUM is AUTO INCREMENT primary key, which i dont want it the output if possible.

Thanks for help in advance :))

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This should return the rows you want, based on the num column:

SELECT Name, lastName, address, people.conID, postNum, region 
FROM people 
JOIN countries 
ON people.conID = countries.conID 
WHERE num=1

Method 2

You can use the following MySQL query:

SELECT
    Name,
    lastName,
    address,
    people.conID,
    postNum,
    region
FROM
    people
JOIN
    countries
        ON people.conID = countries.conID
WHERE
    num = 1

Method 3

This might do the trick:

SELECT name, lastName, address, people.conID, postNum, region
FROM people
 JOIN countries ON people.conID = countries.conID;

Method 4

You SQL query should look like this:

SELECT p.Name, p.lastName, p.address, p.conID, c.postNum, c.region
FROM people p 
LEFT JOIN countries c ON p.conID = c.conID

Method 5

Good ole USING clause:

SELECT Name, lastName, address, p.conID, postNum, region FROM people p
INNER JOIN countries USING (conID);

Method 6

SELECT Name, lastName, address, people.conID, postnum, region
FROM people JOIN countries ON people.conID = countries.conID;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0