CODEWITHSUNDEEP
mysqlsqljoinleft-join
rkeet
rkeet

Reputation: 3468

Join Distinct Id on non-distinct id (MySql)

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?

Upvotes: 0

Views: 147

Answers (1)

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

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

Upvotes: 1

Related Questions