Seems extremely basic, but I can’t find an elegant solution to it. I have two SQL tables, say one contains the users, and the other one, the cities. A third table contains the matches between the two previous tables (with two foreign keys pointing to each of the tables’ PKs.)
I want to add an item to this third table to tell that the user Jeff lives in Boston, MA.
Right now, this is what I do:
INSERT INTO users_countries
SELECT user_id, city_id FROM
(
SELECT TOP 1 user_id
FROM users
WHERE name = 'Jeff' AND
) a JOIN
(
SELECT TOP 1 city_id
FROM cities
WHERE name = 'Boston' AND
) b ON 1 = 1;
While this does the job, it doesn’t look elegant at all.
What would be a better way to write this query?
Advertisement
Answer
Don’t join 2 unrelated tables.
You want to add new row to the table which consists of 2 totally unrelated values.
You are looking for elegance where you should be looking for efficiency first.
Why do you use a join? Even then, why an INNER
join when a CROSS
join would do without setting any conditions?
I would use VALUES
and not SELECT
and a totally unnecessary JOIN
:
INSERT INTO users_countries(user_id, city_id) VALUES
(
(
SELECT TOP 1 user_id FROM users
WHERE name = 'Jeff' AND
),
(
SELECT TOP 1 city_id FROM cities
WHERE name = 'Boston' AND
)
);
See a simplified demo.