CODEWITHSUNDEEP
sqlselect
Stan
Stan

Reputation: 37

sql selection from two tables

i have two tables DETAILS and BANLIST. i search DETAILS table to get list of IPs:

select ip, time, othecol from details WHERE somefield=X

as a result i get zero or N records. at the same time i need to know if returned IP is banned or not. I can check it like this:

select isbanned from banlist WHERE ip=someIP

is it possible to combine both statements in one?

to get a list in format:| ip, time, othercol, isbanned |

Upvotes: 2

Views: 149

Answers (3)

Mike Christensen
Mike Christensen

Reputation: 91598

You can use a JOIN:

SELECT d.ip, d.time, d.othercol, b.isbanned FROM details d
LEFT JOIN banlist b on b.ip = d.ip
WHERE d.somefield=X

Note, b.isbanned will be null for rows in details that have no matching IP in banlist. You can coalesce that value into a false (the syntax for this varies based on what RDMS you're using.)

Upvotes: 2

Alwin Kesler
Alwin Kesler

Reputation: 1520

I think you're trying to do a simple join

SELECT d.ip, b.isbanned
FROM details d
LEFT JOIN banlist b  ON d.ip = b.ip
WHERE foo=bar

Upvotes: 1

David B
David B

Reputation: 2698

Take a look at the INNER JOIN keyword for SQL.

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Upvotes: 0

Related Questions