How to join/subquery a second table

Question

I have two tables, one table has some information in each row along with a comma seperated list of ids that another table contains. Right now I am grabbing the data from table A (with the comma seperated ids), and I want to also grab all of the data from Table B (the table containing additional information). I would like to do this in the most efficient SQL method possible.

I was thinking about joining Table B to Table A based on the ids IN the field, but I was not sure if this is possible. It is also important to note that I am grabbing data from Table A based on another IN statement, so my ultimate goal is to attach all of the rows in Table B to Table A's rows depending on which ids are in the field in Table A's rows (row by row basis)

If someone could follow all of that and knows what I am trying to do I would appreciate a sample query :D

If you need any further clarifaction I would be happy to provide them.

Thanks

The way Table A is setup now:

`table_a_id` VARCHAR ( 6 ) NOT NULL,
`table_b_ids` TEXT NOT NULL, -- This is a comma seperated list at the moment
-- More data here that is irrelevant to this question but i am grabbing

Table B is setup like this:

`table_b_id` VARCHAR ( 6 ) NOT NULL,
`name` VARCHAR ( 128 ) NOT NULL,
-- More data that is not relevant to the question

Also I want to eventually switch to a NOSQL system like Cassandra, from what I have briefly read I understand there are no such things as joins in NOSQL? A bonus help would be to help me to setup these tables so I can convert over with less conversions and difficulty.

Answer 1

You need to add another table.

Person -- your Table A
------
PersonID 

Thing -- your Table B
------
ThingID
ThingName

PersonThing -- new intersection table
-------
PersonID
ThingID

Then your query becomes

SELECT * from Person
INNER JOIN PersonThing ON Person.PersonID = PersonThing.PersonID
INNER JOIN Thing ON PersonThing.ThingID = Thing.ThingID

So where now you have

001 | Sam Spade | 12,23,14

You would have

Person
001 | Sam Spade

Thing
12 | box
23 | chair
14 | wheel

PersonThing
001 | 12
001 | 23
001 | 14

This is what the other answers mean by "normalizing".

Edited to add

From what I understand of NoSQL, you would get around the joins like this:

Person -- your Table A
------
PersonID
OtherPersonStuff

Thing -- your Table B
------
ThingID
ThingName
OtherThingStuff


PersonThing -- denormalized table, one record for each Thing held by each Person
-------
PersonID
ThingID
ThingName
OtherThingStuff

In exchange for taking up extra space (by duplicating the Thing information many times) and potential data management headaches (keeping the duplicates in sync), you get simpler, faster queries.

So your last table would look like this:

PersonThing
001 | 12 | box   | $2.00
001 | 23 | chair | $3.00
001 | 14 | wheel | $1.00
002 | 12 | box   | $2.00
003 | 14 | wheel | $1.00

In this case OtherThingStuff is the value of the Thing.

Answer 2

You should consider normalizing your database schema in order to use a join. Using comma separated lists will not allow you to use any SQL IN commands.

The best way to do it is to store a row for each unique ID, then you can JOIN on TableA.id = TableB.id