Join Distinct Id on non-distinct id (MySql)

Question

I'm trying to join distinct ID's from a subquery in a FROM onto a table which has the same ID's, but non-distinct as they are repeated to create a whole entity. How can one do this? All of my tries are continuously amounting to single ID's in the non-distinct-id-table.

For example:

Table 1
ID    val_string    val_int    val_datetime
1     null          3435         null
1     bla           null         null
1     null          null         2013-08-27
2     null          428          null
2     blob          null         null
2     null          null         2013-08-30
etc. etc. etc. 

Virtual "v_table" from SubQuery
ID
1
2

Now, if I create the query along the lines of:

SELECT t.ID, t.val_string, t.val_int, t.val_datetime
FROM table1 AS t
    JOIN (subquery) AS v_table
        ON t.ID = v_table.ID

I get the result:

Result Table: 
ID    val_string    val_int    val_datetime
1     null          3436       null
2     null          428        null

What I'd like is to see the whole of Table 1 based on this example. (Actual query has some more parameters, but this is the issue I'm stuck on).

How would I go about making sure that I get everything from Table 1 where the ID's match the ID's from a virtual table?

How did val_int change from 3435 in the original table to 3436 in the result? — Barmar, Aug 27 '13 at 09:27
Are the "some more parameters" the reason why your result doesn't have any of the rows with non-null `val_string` or `val_datetime`? It's hard to tell what you're really having a problem with when you leave things out like that. — Barmar, Aug 27 '13 at 09:30
your query seems right: http://www.sqlfiddle.com/#!2/36242/2 — Praveen Prasannan, Aug 27 '13 at 09:32
@JanZeiseweis No I had'nt yet actually *facepalm*, was working of the example from [here](http://stackoverflow.com/questions/5686271/selecting-multiple-columns-fields-in-mysql-subquery). Until just before I had no idea that you could use a subquery as a virtual table. @Barmar that's a typo, and the other parameters aren't the issue for `null` values, still need to work on getting them filled up in the same query. @Praveen yes I do, based on columns from the virtual table which aren't relevant for the problem, and thanks for the fiddle :) @Strawberry, thought the PK's in this issue obvious ;) — rkeet, Aug 27 '13 at 10:02

score 1 · Accepted Answer · answered Aug 27 '13 at 09:27

1

SELECT t.ID, t.val_string, t.val_int, t.val_datetime
FROM table1 AS t
    LEFT JOIN (subquery) AS v_table
        ON t.ID = v_table.ID

Sample fiddle

answered Aug 27 '13 at 09:27

Praveen Prasannan

7,093
10
50
70

Is there a way to speed up the performance on this way of joining? I'm experiencing extreme slow performance running through the database using `LEFT JOIN`. – rkeet Aug 27 '13 at 10:20
For that you need to check your subquery first. Subqueries are not generally recommended as it is slow. If a join can replace a subquery, that will be great. Also add index to fields inside where (id). – Praveen Prasannan Aug 27 '13 at 10:23

Join Distinct Id on non-distinct id (MySql)

1 Answers1