I have 2 tables:
CREATE TABLE plans
(id int, benefit varchar(5), clip_state int);
INSERT INTO plans
(id, benefit, clip_state)
VALUES
(1, 'A', 1),
(2, 'A', 0),
(3, 'B', 0),
(4, 'C', 0);
CREATE TABLE clip_states
(state varchar(2), clip_state int);
INSERT INTO clip_states
(state, clip_state)
VALUES
('LA', 1),
('FL', 0);
Please noted that the clip_state
is either 0 or 1 and the data model allows to query for one or no plan in plans
table from 2 information: benefit
& state
. First, using benefit
condition, we can filter the plans
table to 2 rows at most, one with clip_state
= 0, and one = 1. Then by using state
and join with the clip_states
table, we can reduce the result to one row (or zero) by checking:
state
is in clip_states
table, make sure the clip_state
is matched between 2 tables. If no match, no result returned.state
doesn't exist in clip_states
, it only matches with the row in plans
table that has clip_state
= 0.Here is my query that does the trick:
SELECT id, p.clip_state, benefit
FROM plans p
LEFT JOIN clip_states cs ON STATE IN ('LA')
WHERE benefit = 'A' AND
(p.clip_state = cs.clip_state OR (p.clip_state = 0 AND cs.clip_state IS NULL));
As you can see, the left join is quite strange because it doesn't join on the relationship between the 2 tables. So, my question is:
You can check my solution at: http://sqlfiddle.com/#!1/8912d/53
Updated 1: I have updated the text of the question above.
Updated 2: More information
state
isn't in clip_states
table, its clip_state
implicitly equals to 0. Otherwise, its clip_state
is in the table.benefit
and state
, find a row in plans
table where plans.clip_state
= clip_state
of that given state
. Of course if it doesn't match, no row returned.The answer to this question is yes, you can join two unrelated tables in SQL, and in fact, there are multiple ways to do this, particularly in the Microsoft SQL Server database. The most common way to join two unrelated tables is by using CROSS join, which produces a cartesian product of two tables.
The JOIN or INNER JOIN does not return any non-matching rows at all. It returns only the rows that match in both of the tables you join. If you want to get any unmatched rows, you shouldn't use it. The LEFT JOIN and the RIGHT JOIN get you both matched and unmatched rows.
Syntax For Left Join:SELECT column names FROM table1 LEFT JOIN table2 ON table1. matching_column = table2. matching_column; Note: For example, if you have a left table with 10 rows, you are guaranteed to have at least 10 rows after applying join operation on two tables.
The outer join is needed when you wish to include rows that do not have matching values.
A completely different approach:
"Add" the required state to the clip_states
table with the clip_state
of 0, if that state isn't already in the table.
Inner join the resulting set to plans
on clip_state
, additionally filtering on state
.
This is what it might look like:
SELECT p.*
FROM plans p
INNER JOIN (
SELECT state, clip_state FROM clip_states
UNION ALL
(
SELECT 'LA', 0
EXCEPT
SELECT state, 0 FROM clip_states
)
) cs
ON p.clip_state = cs.clip_state
WHERE p.benefit = 'A'
AND cs.state = 'LA'
;
Here's this query "in action": http://sqlfiddle.com/#!1/b0feb/7
Seems consistent with your requirements, unless I've missed something again.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With