Log in to AnswerLog in to Follow
2022-05-02 13-46-21
Raffa Guerra
16Views
7Comments
Solved
SQL - Select With two Tables
Question
SQL

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

0
0
07 Jun
2018-10-29 08-31-03
João Marques
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

See solution in context
2
0
07 Jun

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.

0
0
07 Jun

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.

0
0
07 Jun
2018-10-29 08-31-03
João Marques
mvp_badge
MVP
Solution

Replying to Raffa Guerra's comment on 07 Jun 2022 16:20:20

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

0
0
07 Jun

Replying to João Marques' comment on 07 Jun 2022 16:41:53

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.

0
0
07 Jun

Replying to Raffa Guerra's comment on 07 Jun 2022 18:21:40

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

0
0
07 Jun
2018-10-29 08-31-03
João Marques
mvp_badge
MVP
Solution

Replying to Raffa Guerra's comment on 07 Jun 2022 16:20:20

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

See solution in context
0
0
07 Jun

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

0
0
07 Jun
2018-10-29 08-31-03
João Marques
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

2
0
07 Jun

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.

0
0
07 Jun

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.

0
0
07 Jun
2018-10-29 08-31-03
João Marques
mvp_badge
MVP
Solution

Replying to Raffa Guerra's comment on 07 Jun 2022 16:20:20

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

0
0
07 Jun

Replying to João Marques' comment on 07 Jun 2022 16:41:53

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.

0
0
07 Jun

Replying to Raffa Guerra's comment on 07 Jun 2022 18:21:40

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

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