-3

I am a beginner in SQL queries/subqueries and I'm having a lot of problems with my code. I have two tables, TableA and TableB. My database is called data.

Contents of tables:

Table A:
IDA (primary key)
ColumnA1

Table B: 
IDB (primary key)
IDA (foreign key)
ColumnB1
ColumnB2

I am attempting to select IDA from TableA and also select a new column I created called NewColumnSubtract which was created as a result of a join between TableA and TableB. The result I want displayed is IDA and NewColumnSubtraction.

Here is an example of my code:

use `data` ;
SELECT 
    IDA,
    ColumnA1 - (ColumnB1 * ColumnB2) AS NewColumnSubtraction
FROM
    `data` . TableA
        JOIN
    `data` . TableB ON TableB.IDA = TableA.IDA;

If I do not include the third line of my code, which is just selecting IDA first, it works and just selects the new column I created. The problem is that I also want to display IDA.

Another problem I am having is that if I do not include 'data' . Table_ (there is no space before and after the . in my actual code), I get an ambiguous error. I know it is bad practice to call the database name in the script, but I do not know how to get around this.

Any help here would be greatly appreciated. Thank you so much!!

iloveteeth17
  • 1
  • 1
  • Since SQL includes data definition, a [(minimal) complete example](https://stackoverflow.com/help/mcve) should include [DDL](https://en.wikipedia.org/wiki/Data_definition_language) statements for sample tables (rather than an ad hoc table specification) and [DML](https://en.wikipedia.org/wiki/Data_manipulation_language) statements for sample data. – outis Apr 27 '21 at 01:57
  • Questions should include both what is expected/desired, and what actual happens. In particular, any error messages should be included verbatim. – outis Apr 27 '21 at 01:58
  • See the help center for more on [how to ask good questions](https://stackoverflow.com/help/how-to-ask) and many other helpful topics. – outis Apr 27 '21 at 01:58

3 Answers3

0

You have to explain to the database which of the IDA columns you want.

You can write the table name before the column or use aliases, so that you must not write that much.

If you have more columns like that , you need also to add the table name or alias.

&To prevent such problems, rty to make unique column names

use `data` ;
SELECT 
    a.IDA,
    ColumnA1 - (ColumnB1 * ColumnB2) AS NewColumnSubtraction
FROM
    TableA a
        JOIN
    TableB b ON b.IDA = a.IDA;

example

 CREATE TABLE TableA (IDA int, ColumnA1 int)




 CREATE TABLE TableB (IDA int, ColumnB1 int, ColumnB2 int)



 SELECT 
     a.IDA,
     ColumnA1 - (ColumnB1 * ColumnB2) AS NewColumnSubtraction
 FROM
     TableA a
         JOIN
     TableB b ON b.IDA = a.IDA;
IDA NewColumnSubtraction

db<>fiddle here

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
nbk
  • 45,398
  • 8
  • 30
  • 47
  • I also thought that using an alias would help. I have already tried this exact solution and I am getting an error located near the column in which I create the new subtraction variable. – iloveteeth17 Apr 24 '21 at 16:35
  • Please share the error message you are getting. – Kazi Mohammad Ali Nur Romel Apr 24 '21 at 16:40
  • " Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '- (ColumnB1 * ColumnB2) AS NewColumnSubtraction FROM TableA a JOIN TableB b ON b.Tabl' at line 2 ". I am using MySQL Workbench 8.0, version 8.0.21. – iloveteeth17 Apr 24 '21 at 16:42
  • @iloveteeth17 i aded a sample code. please check if you didn't make a copy error – nbk Apr 24 '21 at 16:45
  • My tables have already been created and have data stored in them, but they were created in the same way as your code and have the same data types (INT). I have the exact same code and I am still popping the same error. I am beginning to think it has something to do with the fact that nothing will work unless I call my database within my sql script, but not sure where to go from there. If I do not try to select IDA at all and then call the database in the FROM statement with the 2 tables, it works. If I include the selecting of IDA and call the databases, it also still does not work. – iloveteeth17 Apr 24 '21 at 16:51
  • please make a fiddle of yor problem or modify mine and try to repeat the error message – nbk Apr 24 '21 at 16:52
  • I am getting a message that says" "Select" is not valid at this position for this server version, expecting For, Lock, Table, Values, '(' " when I mouse over the word Select at the beginning of my code. – iloveteeth17 Apr 24 '21 at 16:54
  • I just posted a comment with a solution that just worked for me. Let me know if you have any ideas as to why it worked by doing my specific solution.. I'm pretty stumped for an explanation or understanding here. – iloveteeth17 Apr 24 '21 at 17:00
  • please post a dbfiddlwe where you show that problem, adding the data base sin't necessary as you already use data` – nbk Apr 24 '21 at 17:49
  • "To prevent such problems, [try] to make unique column names": that's one style, but having to specify the table for ambiguous column names isn't part of the problem, it's another style. With the unique column name approach, you sometimes end up with redundancies (such as column names prefixed with table names) or a reduction in clarity. The over-arching goal should be clarity & concision. – outis Apr 27 '21 at 02:35
  • i have programs that are running for 20 year, after a time you have to look at the code and still get where the problem is or what y ou were thinking s SELECT * and everywhere the same column really don't help, i query that a hundreds of lines of code. a fix naming cnvention you follow for all tables, helps to maintain your code and futre develeopment – nbk Apr 27 '21 at 07:42
-1

There should not be any syntax error. Please check the query again. Here goes an example.

Create table statements:

 create table TableA(IDA int, ColumnA1 int);
 
 create table TableB(IDB int, IDA int, ColumnB1 int,ColumnB2 int);

Query:

 SELECT 
     a.IDA,
     (a.ColumnA1 - (b.ColumnB1 * b.ColumnB2)) AS NewColumnSubtraction
 FROM
     TableA a
         JOIN
     TableB b ON b.IDA = a.IDA;

Output:

IDA NewColumnSubtraction

db<>fiddle here

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
-1

I found the solution by doing this:

use data ; SELECT TableA.IDA, TableA.ColumnA1 - (TableB.ColumnB1 * TableB.ColumnB2) AS NewColumnSubtraction FROM data . TableA JOIN data . TableB ON TableB.IDA = TableA.IDA;

So, I had to not only call the database for each instance of a table but the table for each instance of a column. Anyone have any ideas as to why this is happening?

iloveteeth17
  • 1
  • 1