My subquery is overwriting a column of the searched table

Question

I’m putting together a list of posts, where besides the post information I need the information of the house that made the post.

I did the following table query posts and casas:

SELECT * FROM posts, (SELECT * FROM casas) AS sqlcasas 
WHERE casa = $c_id AND casa = sqlcasas.id

but this query is overwriting the column id table posts for id of casa. For example:

[
    {
        "id": "9",
        "casa": "9",
        "midia": "/1564714715.png",
        "texto": "...",
        "data": "...",
        "likes": "26",
        "nome": "...",
        "endereco": "...",
        "cidade": "...",
        "telefone": "...",
        "logo": "/1564714696.png",
        "site": "...",
        "facebook": "...",
        "instagram": "..."
    }
]

And in my front-end this endpoint needs to return me both the home id and the post id.

My tables:

    DROP TABLE IF EXISTS `casas`;

CREATE TABLE `casas` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nome` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `endereco` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `cidade` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `telefone` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `logo` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `site` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `facebook` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `instagram` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



# Dump da tabela posts
# ------------------------------------------------------------

DROP TABLE IF EXISTS `posts`;

CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `casa` int(11) unsigned DEFAULT NULL,
  `midia` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `texto` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `data` date NOT NULL,
  `likes` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `casa` (`casa`),
  CONSTRAINT `post_casa` FOREIGN KEY (`casa`) REFERENCES `casas` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I tried this query, but returned an error in my mysqli_query().

SELECT id AS id_post, casa FROM posts (SELECT id AS id_casa FROM casas) WHERE casa = id_casa

Answer 1

Good night. Well, from what I understand you have the home table and the post table, and from the looks of the relationship the tables would be that 1 house can do N posts correct? So I believe that in the modeling of your database you must have created the post table containing a foreign key (which would be the home table id). Something like that:

Table

id_casa
street
number
city

Tablepost id_post
id_casa
textPost
dateHoraPost

If so you can use Join in select:

SELECT a.id_casa,a.rua,a.numero,a.cidade,b.textPost,b.dataHoraPost FROM TabelaCasa as a
       INNER JOIN TabelaPost as b ON a.id_casa = b.id_casa

This way it will bring all the information of the houses that have post along with the post information.

I hope I’ve helped.

[Edit] Now that you included the schema of the tables I was able to see the query problem. Your FOREIGN KEY table POSTS calls "home", so you should use the following query:

$query = "SELECT c.*, p.* FROM casas AS c INNER JOIN posts as p ON c.id = p.casa WHERE c.id = $c_id";