How to SELECT FROM two tables and match two columns in SQL?

Question

So what i'm trying to do is to SELECT all records from table1 that has a certain Project number. Then i will JOIN another table and get only one column named "FloorId" that is equal to both Project AND Element in table1.

(SELECT Project FROM dbo.IMP_MODEL_GEOMETRY WHERE dbo.IMP_GEOMETRY.Project = dbo.IMP_ELEMENT.Project AND dbo.IMP_GEOMETRY.Element = dbo.IMP_ELEMENT.Element)

In the dbo.IMP_GEOMETRY table i will get several results on Project and Element, so maby i can just select the first one.. DISTINCT?

This is what i have tried:

$sql = "SELECT * FROM dbo.IMP_ELEMENT WHERE Project LIKE '%$objNr%'
            INNER JOIN dbo.IMP_MODEL_GEOMETRY ON dbo.IMP_ELEMENT.Project = dbo.IMP_MODEL_GEOMETRY.Project";

I am new to SQL and don't really know what the "." stands for in "dbo.IMP". The table name is: dbo.IMP_ELEMENT (I have seen querys where the dot marks the table and after the dot is the column name. But in this case the dot doesn't represent both table and column, it's just a dot in the table name.

Except in the JOIN where Project is the column (dbo.IMP_MODEL_GEOMETRY.Project)

The table i expect looks something like:

id    Project    Element    FloorId

Where FloorId comes from table2.

EDIT

Both tables contains "Project" and "Element" So, i can use them to match. In table1 there is only one row per Project-Element but in table2 there can be multiple rows with Project-Element, so from table2 it's ok to only select the first match found.

It is not clear what you want to achieve. The IMP_MODEL_GEOMETRY table contains multiple FloorId per Project and Element and you want to arbitrarily pick only one of the FloorId per Project and Element? That would be `select project, element, min(floorid) from imp_model_geometry group by project, element` for instance. is this already what you are looking for? — Thorsten Kettner, Aug 22 '19 at 11:38
What DBMS are you asking this for? Is this SQL Server? Please tag your request with the DBMS you are using. — Thorsten Kettner, Aug 22 '19 at 11:41

Fahmi · Accepted Answer · 2019-08-22T12:01:35.123

3

You can try below way -

SELECT a.id,a.project,a.element,b.floorid FROM dbo.IMP_ELEMENT a
            INNER JOIN dbo.IMP_MODEL_GEOMETRY b ON a.Project = b.Project and a.element=b.element
WHERE a.Project LIKE '%$objNr%'

edited Aug 22 '19 at 12:01

answered Aug 22 '19 at 11:22

Fahmi

37,315
5
22
31

Thank you. But i need to match both Project and Element as it was one value. Match it from table one.. to find it in table2?! – Björn C Aug 22 '19 at 11:26
@BjörnC, I've added that too - you can check now – Fahmi Aug 22 '19 at 11:27
Thank you, i will try it. Is it possible to SELECT * from table1 ? – Björn C Aug 22 '19 at 11:29
@BjörnC, yes just in select write a.* – Fahmi Aug 22 '19 at 11:30
1

Fabulous, with an a. in front of the last WHERE project... it works like a charm. Thank you – Björn C Aug 22 '19 at 11:43
Haha, so the question was simply how to write a proper join. I must admit that a floor ID being "equal" to a project and element and seeing multiple result rows per project and element as a problem obfuscated this for me. +1 for getting the gist of the request ;-) – Thorsten Kettner Aug 22 '19 at 11:52

score 3 · Answer 2 · answered Aug 22 '19 at 11:51

I am new to SQL and don't really know what the "." stands for in "dbo.IMP".

The "." is part of the syntax to identify the element you are after. In full this is .. e.g. Adventureworks.dbo.customer

This extends to columns as well by adding another "." and specifying the column name.

But developers are lazy, and if you do not specify the database then the engine uses the database the query is currently connected to. It is totally normal to not specify a database.

Actually we are really lazy and most don't specify the schema (the dbo. part) as the database engine will just check all schema.

Selecting data - joins

SELECT
    --use table aliases (t1.) for each column, this is shorthand for dbo.table1 
    --if the same column exists in more than one table the database engine knows 
    --which one you want by looking at the aliases  
    t1.column_name 
    ,t2.column_name
FROM 
    dbo.table1 as t1
    INNER JOIN dbo.table2 as t2  
        --inner join returns only rows where the join matches both tables 
        ON t1.primary_key_column = t2.foreign_key_column 
WHERE
    --the join gives you a super table with all the rows that matched the join
    --now you can add in any filters to your results
    t1.some_column = 'foo'
;

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

score 0 · Answer 3 · edited Aug 22 '19 at 11:37

0

Or you can try the below,

select table1.*, table2.FloorId
from IMP_ELEMENT as table1,
     IMP_MODEL_GEOMETRY as table2 
where table1.Project = table2.Project
  and table1.Project like '%Whatever%';

You can change the mail table as whatever you like.

edited Aug 22 '19 at 11:37

jarlh

42,561
8
45
63

answered Aug 22 '19 at 11:26

Sathiyadasan

33
7

1

Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed – jarlh Aug 22 '19 at 11:37

How to SELECT FROM two tables and match two columns in SQL?

3 Answers3