SQL conditional data display based on multiple tables and columns

Question

I am attempting to display several columns of tableX data when the value of a specific column matches that of the corresponding column in tableY.

Imagine we have 2 tables: one named person containing a column named membershipid and the other named taskscontaining a column named memberid. In this case, the condition for display a record would be the following:

membershipid on person = memberid on tasks

Does this require the use of a JOIN clause or can I still use the WHERE keyword for this?

Thanks for taking a look.

@speshock Did any of the answers below help to answer your question? — Steve Chambers
– Steve Chambers, Commented Jun 5, 2013 at 14:43

Steve Chambers · Accepted Answer · 2016-01-25 20:41:27Z

2

From what I've understood of your question, it sounds like yes you'll need an INNER JOIN to pull out only the rows from tableX where there is a matching value in the joined column in tableY.

So something like:

SELECT tableXcol1, tableXcol2, tableXcol3
FROM tableX
INNER JOIN tableY
ON tableX.specific_column = tableY.specific_column

edited Jan 25, 2016 at 20:41

answered Apr 25, 2013 at 21:55

Steve Chambers

39.8k29 gold badges178 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barranka · Accepted Answer · 2013-04-25 21:57:30Z

1

ALthough it can be done with where clauses, I would recommend you to use joins.

The where style (not recommended)

select t1.field1, t1.field2, ...
from aTable as t1, anotherTable as t2
where t1.field1 = t2.field1;

The joinstyle (recommended)

select t1.field1, t1.field2, ...
from aTable as t1 inner join anotherTable as t2 on t1.field1 = t2.field1

answered Apr 25, 2013 at 21:57

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Comments

sayap · Accepted Answer · 2013-04-26 01:14:00Z

1

If you only need data from tableX but not from tableY, doing a JOIN would likely give you duplicate data. Use EXISTS instead, e.g.

SELECT person.col1, person.col2
FROM person
WHERE
    EXISTS (
        SELECT 1
        FROM tasks
        WHERE
            person.membershipid = tasks.memberid
    )

answered Apr 26, 2013 at 1:14

sayap

6,2852 gold badges38 silver badges40 bronze badges

Collectives™ on Stack Overflow

SQL conditional data display based on multiple tables and columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related