SQL - Select With two Tables
Question

Hi Guys, I'm trying to select a book that is related to a certain author.

For that I'm using this:


SELECT {Livro}.*, {Pessoa}.[Nome], {Pessoa}.[Sobrenome] FROM {Livro}, {Pessoa}
INNER JOIN {Pessoa}
WHERE {Pessoa}.[Nome] LIKE @Autor OR {Pessoa}.[Sobrenome] LIKE @Autor

It's just not working and I'm kind of a layman in SQL.

Screen Shot 2022-06-07 at 12.06.18.png

mvp_badge
MVP
Solution

Hi Raffa,


Your query should be something like this:


SELECT {Livro}.*, {Pessoa}.[Nome], {Pessoa}.[Sobrenome]
FROM {Livro} INNER JOIN {Pessoa} ON {Livro}.[PessoaId] = {Pessoa}.[Id]
WHERE {Pessoa}.[Nome] LIKE '%' + @Author + '%' OR {Pessoa}.[SobreNome] LIKE '%' + @Author + '%'



The part I write on bold is how you connect the two tables. I don't know how do you connect them so you may need to adjust the attributes on both tables so you can join them correctly.


Kind Regards,
João

Hi João,

I had to make a change and it looked like this:


SELECT {Book}.*, {Person}.[First Name], {Person}.[Last Name]

FROM {Book} INNER JOIN {Person} ON {Person}.[BookId] = {Book}.[Id]

WHERE {Person}.[First Name] LIKE '%' + @Author + '%' OR {Person}.[Last Name] LIKE '%' + @Author + '%'


Because the relationship between the tables is that 1 Book can have N People (Authors, translators, coordinators, cover designer, etc.)


That way, the error stopped. I hope I got it right in the construction of tables and relations, but if I'm wrong I'll learn.

However, it still gives this error:

Unexpected SQL

There is a mismatch between the number of columns SELECT'ed and the output structure in SQL_Author.

mvp_badge
MVP
Solution

Hi Raffa,


The error states that the output structure does not match the output of your query.

For performance reasons, your output should contain only the number of fields you need, therefore things such {Book}.* are disadvised.

My suggestion is therefore to:

  1. create the structure with the things you need (for example BookName, PersonFirstName and PersonLastName)
  2. adjust your SELECT instruction to only include these attributes.


Kind Regards,
João

Okay, would that be it?


SELECT {Book}.[Book_Name] CollectionName, {Person}.[Name]+' '+ {Person}.[LastName] FullAuthorName

     FROM {Book} INNER JOIN {Person} ON {Person}.[BookId] = {Book}.[Id]

     WHERE {Person}.[First Name] + ' ' +{Person}.[Last Name] LIKE '%' + @Author + '%'

ORDER BY NameAsset


I created a Structure with NameAcervo andNameCompleteAuthor, but it remains with the same message.

I achieved:


SELECT {Book}.Id, {Book}.[Book_Name]CollectionName, {Person}.[Name]+' '+ {Person}.[LastName] FullAuthorName

     FROM {Book} INNER JOIN {Person} ON {Person}.[BookId] = {Book}.[Id]

     WHERE {Person}.[First Name] + ' ' +{Person}.[Last Name] LIKE '%' + @Author + '%'

ORDER BY NameAsset

mvp_badge
MVP
Solution

Hi Raffa,


The error states that the output structure does not match the output of your query.

For performance reasons, your output should contain only the number of fields you need, therefore things such {Book}.* are disadvised.

My suggestion is therefore to:

  1. create the structure with the things you need (for example BookName, PersonFirstName and PersonLastName)
  2. adjust your SELECT instruction to only include these attributes.


Kind Regards,
João

Hi Raffa,

Correct Query is like below

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name

Highlighted is missing from your query.

SELECT {Livro}.*, {Pessoa}.[Nome], {Pessoa}.[Sobrenome] FROM {Livro}

INNER JOIN {Pessoa}    ON {Livro}.[column_name] = {Pessoa}.[column_name]

WHERE {Pessoa}.[Nome] LIKE @Autor OR {Pessoa}.[Sobrenome] LIKE @Autor


Best Regards

Devendra

mvp_badge
MVP
Solution

Hi Raffa,


Your query should be something like this:


SELECT {Livro}.*, {Pessoa}.[Nome], {Pessoa}.[Sobrenome]
FROM {Livro} INNER JOIN {Pessoa} ON {Livro}.[PessoaId] = {Pessoa}.[Id]
WHERE {Pessoa}.[Nome] LIKE '%' + @Author + '%' OR {Pessoa}.[SobreNome] LIKE '%' + @Author + '%'



The part I write on bold is how you connect the two tables. I don't know how do you connect them so you may need to adjust the attributes on both tables so you can join them correctly.


Kind Regards,
João

Hi João,

I had to make a change and it looked like this:


SELECT {Book}.*, {Person}.[First Name], {Person}.[Last Name]

FROM {Book} INNER JOIN {Person} ON {Person}.[BookId] = {Book}.[Id]

WHERE {Person}.[First Name] LIKE '%' + @Author + '%' OR {Person}.[Last Name] LIKE '%' + @Author + '%'


Because the relationship between the tables is that 1 Book can have N People (Authors, translators, coordinators, cover designer, etc.)


That way, the error stopped. I hope I got it right in the construction of tables and relations, but if I'm wrong I'll learn.

However, it still gives this error:

Unexpected SQL

There is a mismatch between the number of columns SELECT'ed and the output structure in SQL_Author.

mvp_badge
MVP
Solution

Hi Raffa,


The error states that the output structure does not match the output of your query.

For performance reasons, your output should contain only the number of fields you need, therefore things such {Book}.* are disadvised.

My suggestion is therefore to:

  1. create the structure with the things you need (for example BookName, PersonFirstName and PersonLastName)
  2. adjust your SELECT instruction to only include these attributes.


Kind Regards,
João

Okay, would that be it?


SELECT {Book}.[Book_Name] CollectionName, {Person}.[Name]+' '+ {Person}.[LastName] FullAuthorName

     FROM {Book} INNER JOIN {Person} ON {Person}.[BookId] = {Book}.[Id]

     WHERE {Person}.[First Name] + ' ' +{Person}.[Last Name] LIKE '%' + @Author + '%'

ORDER BY NameAsset


I created a Structure with NameAcervo andNameCompleteAuthor, but it remains with the same message.

I achieved:


SELECT {Book}.Id, {Book}.[Book_Name]CollectionName, {Person}.[Name]+' '+ {Person}.[LastName] FullAuthorName

     FROM {Book} INNER JOIN {Person} ON {Person}.[BookId] = {Book}.[Id]

     WHERE {Person}.[First Name] + ' ' +{Person}.[Last Name] LIKE '%' + @Author + '%'

ORDER BY NameAsset

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.