0

I looked around for a similar question, but if it exists, I haven't found it.

I do not believe this is a duplicate because I am trying to take multiple rows from TableB and combine with a single row from tableA, which is not something shown in the other question.

Consider the following tables.

|TableA       |
|id     value1|
|1     |3     |
|2     |10    |
|3     |10    |
|4     |6     |
|5     |10    |

|TableB              |
|id     Aid    value2|
|1     |1     |14    |
|2     |1     |93    |
|3     |2     |30    |
|4     |3     |12    |
|5     |3     |62    |
|6     |3     |2     |
|7     |4     |2     |
|8     |4     |29    |
|9     |5     |50    |

I want each row FROM TableA WHERE value1=10, but I also want them joined with all the rows in TableB that have the id of those TableA rows as their Aid value.

Basically, I want to get back 3 rows: TableA rows 2, 3, and 5, where the 1st row is joined with TableB's 3rd row, the 2nd is joined with TableB's 4th, 5th, and 6th rows, and the 3rd is joined with TableB's 9th row.

How would I go about doing this? Is it even possible with one query? If it is possible, what would be the result?

This is about as far as I got...

SELECT a.*, b.* FROM TableA AS a JOIN TableB AS b ON a.id=b.Aid WHERE a.value1=10
Dinopolis
  • 153
  • 1
  • 8
  • dupe target, 2nd answer, from Daniel – Drew Oct 07 '16 at 04:12
  • Thank you for the assistance, but I don't think this is really a duplicate for the reason now stated in the question itself. – Dinopolis Oct 07 '16 at 14:54
  • I believe it is shown and is simplistic join syntax with aliases, decisions of which columns to show, and a where clause. If you are still struggling with this in an hour, ping me with an @ sign after giving a url to a [sqlfiddle](http://stackoverflow.com/questions/38899464) – Drew Oct 07 '16 at 16:53

3 Answers3

0
SELECT a.*, b.* 
FROM TableA AS a 
JOIN TableB AS b ON a.id = b.Aid 
WHERE a.value1 = 10
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Miner_Glitch
  • 537
  • 4
  • 16
0

Do you mean

SELECT a.*, b.* FROM TableA AS a JOIN TableB AS b ON a.id=b.Aid WHERE a.value1 = 10

?

somnium
  • 1,487
  • 9
  • 15
0

Better to use the words INNER JOIN so it's clear when you look at this code again what join you use

SELECT a.*, b.* FROM TableA AS a INNER JOIN TableB AS b ON a.id=b.Aid
WHERE a.value1=10

The last bit I added to solve your problem

middlestump
  • 1,035
  • 8
  • 22
  • Thank you, but I'm not sure I made it clear enough what I'm trying to do. I for each tableA row where value1=10, I'd like to join it with however many rows in tableB have Aid that is equal to the id in table one. This should result in one row for each tableA row where value1=10, or 3 total rows. – Dinopolis Oct 07 '16 at 14:58