0

I have a question which seems very basic to me, but cannot find the answer to it. I have two tables: A & B. Table A has a column with id-s (int), table B connects those id-s with descriptions (varchar.) I want to do a query on table A, where I replace the id-s with the descriptions from table B (one-one relation). I currently have this query:

select tableA.* from tableA join tableB on (tableA.id=tableB.description);

This should do the job, apart from that I get Warning 1292 : "Truncated incorrect DOUBLE value".

I understand what it means, the two data types don't match. But then how to make it work? I am sure this must be a simple fix and that what I ask for is being used all the time (e.g. replace ISBN with book title, etc.)...

Any help would be much appreciated!

Edit upon request

Sorry people, I thought it was not ambiguous... Db structure:

mysql> describe tasks;
+----------+-----------------------+------+-----+---------+----------------+
| Field    | Type                  | Null | Key | Default | Extra          |
+----------+-----------------------+------+-----+---------+----------------+
| tid      | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| pid      | int(10) unsigned      | YES  |     | NULL    |                |
| username | varchar(15)           | YES  |     | NULL    |                |
| task     | varchar(1000)         | NO   |     | NULL    |                |
+----------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> describe projects;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| pid   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title | varchar(200)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

I want to "select * from tasks", where every pid is replaced with the corresponding title from table projects in the same query.

Hope it's now clearer...

Willem van Gerven
  • 1,407
  • 1
  • 17
  • 24
  • It would be a *simple question* if you provide table structure, sample data and desired result. – Hotdin Gurning Jan 22 '16 at 09:44
  • 1
    It is pretty weird database design if your "ID" in tableA is of type int and this so called "ID" is a "description" in tableB from type varchar. Please provide your database structure and we can show you how it should look like. – GreenTurtle Jan 22 '16 at 09:49
  • @Abhik Chakraborty: Can you reopen please so I can change this comment to an answer since the problem is clear now after the question was edited. – GreenTurtle Jan 22 '16 at 14:52
  • Ok done its re-opened. – Abhik Chakraborty Jan 22 '16 at 15:10

1 Answers1

0

You can simply use the following query to join the contents of both tables getting all entries from the task table even if they have no project attached:

SELECT t.tid, t.username, t.task, p.title 
FROM tasks t 
LEFT JOIN projects p ON t.pid = p.pid;

If you just want tasks with an attached project use this query instead:

SELECT t.tid, t.username, t.task, p.title 
FROM tasks t 
JOIN projects p ON t.pid = p.pid;
GreenTurtle
  • 1,144
  • 2
  • 21
  • 35