Skip to content
Advertisement

How to select two values in one row?

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:

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:

See a simplified demo.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement