Joining one table all columns and second table few columns

Question

I have two tables:- PERSON and DATA. What I want to do is fetch all details from PERSON table and only two columns from DATA table only when PERSON.personId = DATA.personId.

I am using this query:-

SELECT *
FROM PERSON AND SELECT DATA.value, DATA.field
FROM DATA where PERSON.personId = DATA.personId;

But I think this is wrong syntax. Can anyone tell me what is the right syntax for it.

I think you need to have a look at the MySQL [manual](https://dev.mysql.com/doc/refman/5.7/en/join.html). Look for the syntax of `JOIN`. There are a lot examples there as well. — Giorgos Betsos, Jun 13 '17 at 08:39
Sit down with any introductory book or tutorial, then get back to us with any questions you may have. — Strawberry, Jun 13 '17 at 08:39
If this question is solved then please consider accepting an answer (not necessarily mine). Thank you. — mahyard, Jun 14 '17 at 06:31

score 3 · Answer 1 · answered Jun 13 '17 at 08:44

3

SELECT p.*,d.column1,d.column2
  FROM Person p 
  JOIN Data d
       ON p.personId = d.personId
WHERE <Condition>

In this query person with all columns and data with your desire column you can fetch by this query.

answered Jun 13 '17 at 08:44

Jani Devang

1,099
12
20

score 2 · Answer 2 · answered Jun 13 '17 at 08:41

Something like this:

  select
        P.*,
        D.value,
        D.field
    from Person P
    join Data D on P.PersonID = D.PersonID

change P.* to the specific columns that you need but P.* will get everything from the Person table.

check this post out LEFT JOIN vs. LEFT OUTER JOIN in SQL Server to learn about JOINS, the diagram is good to understand what the different ones do

mahyard · Answer 3 · 2017-06-13T09:06:27.850

1

Its really easy, Just execute this query:

SELECT 
    PERSON.*,
    DATA.value,
    DATA.field
FROM 
    PERSON INNER JOIN DATA USING (`personId`);

It selects all fields of PERSON + value and field from DATA. Also it uses personId to join the two tables.

Fill free to ask if you need more info.

edited Jun 13 '17 at 09:06

answered Jun 13 '17 at 08:42

mahyard

1,230
1
13
34

score 0 · Answer 4 · edited Mar 31 '22 at 06:49

Here's the correct syntax for achieving what you've asked for:

SELECT PERSON.column1,PERSON.column2,PERSON.columnN,DATA.value
FROM PERSON
INNER JOIN DATA 
ON PERSON.personId = DATA.personId

Line#1: lists the columns that you want to select with references to their parent tables.
Line#2 and 3: are the two tables that you want to select from and join with
Line#4: is the join condition between the two tables (with matching IDs or other information)

score 0 · Answer 5 · answered Jun 13 '17 at 08:44

0

You can use join (LEFT JOIN)

SELECT * FROM PERSON LEFT JOIN DATA ON PERSON.personId = DATA.personId

Hope it will help you

answered Jun 13 '17 at 08:44

Alex

3,646
1
28
25

Joining one table all columns and second table few columns

5 Answers5