SQL select and join values for all rows

Question

Imagine that we have Tbl_Names and Tbl_General_Settings tables like that

Tbl_User

id   name    surname
1    Ahmet   ABC
2    Ali     XYZ
3    Selim   KLM

Tbl_Global_Settings

id   Setting1    Setting2    Setting3    Setting 4
1    on          off         yes         no

I need a SELECT query that shows those one row global settings for all rows in the tbl_user:

Query result should be like this:

id   name    surname   Setting1    Setting2    Setting3    Setting 4
1    Ahmet   ABC       on          off         yes         no
2    Ali     XYZ       on          off         yes         no
3    Selim   KLM       on          off         yes         no

How can i do that? thanks..

Answer 1

In your sample, you try to perform a cartesian product.

This operation can be performed in MySql (and other DBMS) with CROSS JOIN, as follow:

SELECT *
FROM Tbl_User u
CROSS JOIN Tbl_Global_Settings gs

CROSS JOIN (Cartesian product) take each row of the first table and create a link with every row of the second table. In your example you have 3 rows as result because 3 (users) x 1 (global settings) = 3.

If you have 2 global setting the result will be 6. (3 x 2)