I have this query in SQL Server:
SELECT r.id_referencia
,t.id_tipo
,o.id_operacion
,p.id_provincia
,s.id_superfice
,pr.id_precio
,v.id_vendedor
,f.id_fecha
FROM vendedor v
JOIN Inmuebles i ON v.nombre_vendedor = i.vendedor
JOIN referencia r ON r.id_referencia = i.Referencia
JOIN Tipo t ON t.nombre_tipo = i.Tipo
JOIN operacion o ON o.nombre_operacion = i.Operacion
JOIN provincia p ON p.nombre_provincia = i.Provincia
JOIN superficie s ON s.superficie = i.Superficie
JOIN precio_venta pr ON pr.valor_venta = i.Precio_Venta
JOIN (
SELECT f.id_fecha
FROM (
SELECT fecha_alta
,YEAR(fecha_alta) AS año
,MONTH(fecha_alta) AS mes
,DAY(fecha_alta) AS dia
,1 AS marca
FROM inmuebles
UNION
SELECT fecha_venta
,YEAR(fecha_venta) AS año
,MONTH(fecha_venta) AS mes
,DAY(fecha_venta) AS dia
,2 AS marca
FROM inmuebles
) AS fec
JOIN fecha f ON fec.año = f.año
WHERE fec.mes = f.mes
AND fec.dia = f.dia
AND fec.marca = f.marca
) AS tiempo
CROSS JOIN fecha f ON tiempo.id_fecha = f.id_fecha
ORDER BY r.id_referencia
It shows me the data as I want but in the id_fecha field it repeats itself to me and the table is left like this:
+------+-------+-------+-------+-------+-------+------+----------+
| id_r | id_t | id_o | id_p | id_s | id_p | id_v | id_fecha |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 1 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 2 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 3 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 4 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 5 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 6 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 7 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 8 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 9 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 10 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 11 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 12 |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 13 |
+------+-------+-------+-------+-------+-------+------+----------+
What part of the query is wrong?
must be something like this (the numbers that can not be equal are the id_r and the date_id)
+------+-------+-------+-------+-------+-------+------+----------+
| id_r | id_t | id_o | id_p | id_s | id_p | id_v | id_fecha |
+------+-------+-------+-------+-------+-------+------+----------+
| 1 | 4 | 2 | 1 | 252 | 2994 | 5 | 1 |
+------+-------+-------+-------+-------+-------+------+----------+
| 2 | 7 | 8 | 6 | 252 | 2994 | 5 | 2 |
+------+-------+-------+-------+-------+-------+------+----------+
| 3 | 4 | 2 | 1 | 753 | 2994 | 5 | 3 |
+------+-------+-------+-------+-------+-------+------+----------+
| 4 | 7 | 2 | 1 | 252 | 2994 | 5 | 9 |
+------+-------+-------+-------+-------+-------+------+----------+
| 5 | 4 | 2 | 1 | 252 | 2994 | 5 | 586 |
+------+-------+-------+-------+-------+-------+------+----------+
| 6 | 4 | 2 | 1 | 252 | 2994 | 5 | 6 |
+------+-------+-------+-------+-------+-------+------+----------+
| 7 | 4 | 2 | 1 | 252 | 2994 | 5 | 43 |
+------+-------+-------+-------+-------+-------+------+----------+
| 8 | 4 | 10 | 345 | 752 | 5694 | 8 | 87 |
+------+-------+-------+-------+-------+-------+------+----------+