CODEWITHSUNDEEP
sql
HelloWorld_PyNoob
HelloWorld_PyNoob

Reputation: 41

SQL: Querying between two tables

I have a table called mapunit in which there is a column called muname and the primary key is called mukey.

I have another table called SpatialJoin, which also has the primary key as mukey. For each row in SpatialJoin, I want to retrieve the corresponding muname.

I tried:

SELECT muname 
FROM mapunit 
WHERE EXISTS (SELECT mukey FROM SpatialJoin)

but that didn't work. Any suggestions for how I could rewrite my statement would be appreciated. Thanks!

Upvotes: 2

Views: 2833

Answers (5)

marc_s
marc_s

Reputation: 754538

Try this:

SELECT
    mu.MuName, mu.mukey
FROM
    SpatialJoin sj
INNER JOIN
    Mapunit mu ON sj.mukey = mu.mukey

The INNER JOIN will "join" the two tables on the defined JOIN condition, and now you can select columns from both tables in your SELECT. The INNER JOIN also joins only rows from both tables that have that common column (and the same value in that common column).

Update: to get CompName from a third Component table, try this:

SELECT
    mu.MuName, mu.mukey, c.CompName
FROM
    SpatialJoin sj
INNER JOIN
    Mapunit mu ON sj.mukey = mu.mukey
INNER JOIN
    Component c ON sj.mukey = c.mukey

Upvotes: 0

Kishore Kumar
Kishore Kumar

Reputation: 12874

SELECT muname
FROM SpatialJoin s
JOIN mapunit m ON m.mukey = s.mukey

try this may work

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

You're picking all rows from from mapunit where the key exists in SpatialJoin, not quite what you want.

Try selecting every row from SpatialJoin instead and just join with mapunit to get the name;

SELECT muname
FROM SpatialJoin sj
JOIN mapunit mu ON mu.mukey = sj.mukey

Upvotes: 1

John Woo
John Woo

Reputation: 263723

try this:

SELECT b.muname
FROM SpatialJoin a INNER JOIN mapunit b
        on a.mukey = b.mukey

Upvotes: 0

Hans Leautaud
Hans Leautaud

Reputation: 1752

SELECT m.muname
FROM mapunit m, SpatialJoin s
WHERE m.mukey=s.mukey

Upvotes: 0

Related Questions