[RESOLVED] Two id's in one table to select multiple rows in another?

gwerner

I have a table that contains two ids. I'd like to select the data from the first table along with two rows from additional tables. Maybe I'm missing the obvious here, how do I grab multiple rows when I'm starting with two id's?

Table 1 = Intial SELECT
id | document1_id | document2_id

SELECT FROM table1 document_id1, document_id2

Table 2
document_id | document_title | document_description

I need to get the two rows that match document_id1 and document_id2. This is where I started to nest selects. This hasn't been working. I could do two JOINS here?

Table 3
document_id | document_filename

I also need two rows here that also match document_id1 and document_id2. Seems like whatever I do above should also work for here.

I've shortened the number of actual columns in the tables to simply illustrate what I'm hoping to accomplish. To begin with, does this look like a good setup for the data? If yes, is there a simple method to select the data? Currently, I have a bunch of nested selects which is getting out of control and seems to be counter-productive. Anyways, as always any help or guidance is appreciated. Thanks.

bradgrafelman

Can you explain more about the actual purpose of Table 1?

Using your current schema, it sounds like you're wanting a query such as:

SELECT
  t1.document1_id d1_id, t1.document2_id d2_id, t2.document_id d_id,
  t2.document_title d_title, t2.document_description d_desc,
  t3.document_filename d_fname
FROM
  Table1 t1
    INNER JOIN Table2 t2
      ON t2.document_id IN (t1.document1_id, t1.document2_id)
    INNER JOIN Table3 t3
      ON t3.document_id = t2.document_id

(untested)

gwerner

The purpose of the first table is to allow me to link two documents together for review purposes. These documents would be linked permanently once that connection is created. I will need to keep this link data indefinitely.

Not sure if that's what you were asking, but maybe it'll help.

bradgrafelman

I was just asking to make sure I understood the schema correctly.

Did you try my example query above? I didn't have access to a SQL server when I wrote it (hence the 'untested' notice), but it seems like it should do what you want.

gwerner

I had another issue arise, so I couldn't get back to this until now. The query you supplied doesn't seem to get exactly what I need. But, after re-reading my original post I wonder if I worded it wrong.

The result set that I'm looking to achieve is:
1 row from table 1
table1_id, document1_id, document2_id

2 rows from table 2
document_title, document_description that matches document1_id AND
document_title, document_description that matches document2_id

2 rows from table 3
document_filename that matches document1_id AND
document_filename that matches document2_id

Hopefully this makes sense. THe supplied query above seems to only return single rows from table 2 and table3. Thanks again.

tomhath

Again, untested:

SELECT
  t1.document1_id, t1.document2_id,
  t21.document_title as t21_title, t21.document_description as t21_description,
  t22.document_title as t22_title, t22.document_description as t22_description,
  t31.document_filename as t31_filename,
  t32.document_filename as t32_filename
FROM
  Table1 t1
    INNER JOIN Table2 t21
      ON t21.document_id = t1.document1_id
    INNER JOIN Table2 t22
      ON t22.document_id = t1.document2_id
    INNER JOIN Table3 t31
      ON t31.document_id = t1.document1_id
    INNER JOIN Table3 t32
      ON t32.document_id = t1.document2_id

gwerner

That worked perfectly Tom. Exactly what I needed. Thanks to both of you for helping. I was able to see two new approaches to querying I had not seen before. They both made sense. I really didn't know it could be done that way. I need to give querying some quality time it appears.