CODEWITHSUNDEEP
mysqlsqldatabase
sg055322
sg055322

Reputation: 161

Joining one table all columns and second table few columns

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.

Upvotes: 1

Views: 6948

Answers (5)

wijaya
wijaya

Reputation: 152

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)

Upvotes: 0

mahyard
mahyard

Reputation: 1238

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.

Upvotes: 1

Jani Devang
Jani Devang

Reputation: 1109

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.

Upvotes: 3

Alex
Alex

Reputation: 4266

You can use join (LEFT JOIN)

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

Hope it will help you

Upvotes: 0

dbajtr
dbajtr

Reputation: 2044

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

Upvotes: 2

Related Questions