SQL query to check multiple values of multiple columns

Question

I have a homework assignment that I can't seem to figure out.

I don't want the entire query as an answer, that's why I won't include any table structures or SQL. I just need some information on how to tackle this issue in SQL. Any help will be appreciated!

Given the following result table, how can I check (per row) if the Location_ID value of ID is the same as the Location value of ID's Substitute? The query should give me a new table for every case where the locations don't match.

Example result table

E.G. ID 1 has a location of 45 and 5 as Substitute. ID 5 has a location of 67, so they should be listed in the result table of the query.

ID 3 has a location of 34 and 2 as a substitute. ID 2 also has a location of 34, so ID 3 should be left out of the result table.

apomene · Accepted Answer · 2014-09-05 13:39:15Z

1

the Idea is to use a self join, something like:

select * from Mytable as a join Mytable as b on a.ID=b.ID
where a.Substitute!=b.Location_ID

answered Sep 5, 2014 at 13:39

apomene

14.4k10 gold badges51 silver badges76 bronze badges

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

Comments

Daniel · Accepted Answer · 2014-09-05 14:14:17Z

1

Thanks, I needed the self join in this case! I had to change the query a bit though, to check against both the ID's locations.

    select * from Mytable as a join Mytable as b on a.ID=b.ID 
    where a.Location_ID!=b.Location_ID

Much appreciated!

answered Sep 5, 2014 at 14:14

Daniel

437 bronze badges

Collectives™ on Stack Overflow

SQL query to check multiple values of multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related