Joining two SQL tables

Question

I have two database tables with some common IDs. This is the structure of the first table:

ID 1 - House 1 - New york - 0100 xxx xxx
ID 2 - House 2 - London - 0100 yyx yyx

This is the structure of the second table:

ID 101 - House 1 - Human 1 - 0100 yyy yyy
ID 102 - House 1 - Human 2 - 0100 xyx yxy
ID 103 - House 2 - Human 3 - 0100 xxy xxy

I would like to find a way to join the two tables together to result in something similar to the table below:

House 1 - New York - 0100 xxx xxx - Human 1 - 0100 yyy yyy, Human 2 - 0100 xyx yxy

UPDATE:

This is current query:

SELECT a.SO as ID, a.NOB as house, a.TEL as tel, o.IME_PREZIME as person,     o.MOBILNI_TELEFON as tel2
FROM OBJEKAT a
join OBJEKAT_KONTAKTOSOBA o on o.SO=a.SO
where a.MOBILNI_TELEFON is not null and o.MOBILNI_TELEFON is not null
order by 2

And this is what i get (two or more rows for each human, and i want it under one row with multiple columns)

16  01NI    018.522.885     Marija Radosavljević    064.844.1858
17  02NI    018.243.568     Dragana Grujić  064.844.1833
17  02NI    018.243.568     Adriana Cvetković   064.844.1867
15  04SK    021.465.263     Nikolina Lulić  064.844.1860
15  04SK    021.465.263     Dragana Borovčanin  064.844.1852
12  05KA    024.877.077     Milena Lončar   064.844.1724

As you can see, under ID 17 and 15 I have two rows and I want It to be one like I show you in first explanation.

THIRD EDIT: What i want is this:

17  02NI    018.243.568     Dragana Grujić  064.844.1833  Adriana Cvetković 064.844.1867

FOURTH EDIT: This is not duplicate, above duplicate suggestions is for mysql, and i have Firebird, whis doesn't have GROUP_CONCAT function

You can use a group by with a `GROUP_CONCAT` for the list of the lodger, if you want a column for every lodger it'll be more difficult — Serpiton, Apr 15 '15 at 10:19
Is it the Houses that's the common factor, i.e. JOIN-ing columns? What do you expect to happen if Human 3 suddenly shows up? — jarlh, Apr 15 '15 at 10:22
I expect another two columns for that human 3 too (name and number) under same house. — Саша Стефановић, Apr 15 '15 at 10:28
Standard SQL can't do that. Perhaps MySQL pivot functionality exists? — jarlh, Apr 15 '15 at 10:51
possible duplicate of [MySQL dynamic-pivot](http://stackoverflow.com/questions/12630128/mysql-dynamic-pivot) — jpw, Apr 15 '15 at 10:54
If your question is not about MySQL please put the appropriate tags — Serpiton, Apr 15 '15 at 16:40

score 0 · Answer 1 · edited Apr 15 '15 at 10:29

0

Your question is not totally clear. But from what I understand, what you need is a foreign key on table2 that references a primary key on table1, something like table2.person_id(fk) references table1.id(primary).

Then your sql query would be something like :

"SELECT * FROM table1,table2 ON table1.id = table2.person_id WHERE ..."

hope this helps

edited Apr 15 '15 at 10:29

Arulkumar

12,966
14
47
68

answered Apr 15 '15 at 10:14

Tunde Demu

11
3

Nope, i know how can i create relations, but i dont how how to put those 2 humans that belongs to house 1 under same row of house 1. Right now, i got two rows for that two humans, and i want it under 1. – Саша Стефановић Apr 15 '15 at 10:24

Stanislovas Kalašnikovas · Answer 2 · 2015-04-15T10:42:19.497

0

If you have following column names:

CREATE TABLE Table1
(
  Id INT,
  HouseName NVARCHAR(60),
  City NVARCHAR(60),        
  Col4 NVARCHAR(60)  
);
CREATE TABLE Table2
(
  Id INT,
  HouseName NVARCHAR(60),
  HumanName NVARCHAR(60),        
  Col4 NVARCHAR(60)  
);
INSERT INTO Table1 VALUES 
(1, 'House 1', 'New york', '0100 xxx xxx'),
(2, 'House 2', 'London', '0100 yyx yyx');

INSERT INTO Table2 VALUES 
(101, 'House 1', 'Human 1', '0100 yyy yyy'),
(102, 'House 1', 'Human 2', '0100 xyx yxy'),
(103, 'House 2', 'Human 3', '0100 xxy xxy');

SELECT t1.HouseName, t1.City, t1.Col4, t2.HumanName, t2.Col4, t3.HumanName, t3.Col4
FROM Table1 t1
JOIN Table2 t2
   ON t1.HouseName = t2.HouseName
JOIN Table2 t3
   ON t2.Id < t3.Id
WHERE t2.HouseName = t3.HouseName

SQL FIDDLE

edited Apr 15 '15 at 10:42

answered Apr 15 '15 at 10:23

Stanislovas Kalašnikovas

9,203
4
32
59

You need to join table2 twice, to get two humans on same row. (LEFT JOIN, in case of less than 2 humans...) – jarlh Apr 15 '15 at 10:26
With this, it's like comment above. i just got relations and again two rows for each person and i want one row for one house, and to be merged under that. So, each person will have separate column under row of hosue 1 – Саша Стефановић Apr 15 '15 at 10:27
Updated answer, try something like that – Stanislovas Kalašnikovas Apr 15 '15 at 10:33
But this is fixed with 2 human names and col4, i somewhere have only 1 data and somewhere more than 5 – Саша Стефановић Apr 15 '15 at 10:41
You should provide sample data, make sql fiddle to understand what you want. – Stanislovas Kalašnikovas Apr 15 '15 at 10:45

Joining two SQL tables

2 Answers2