select all where ID = value, but also grab where ID = column value if not empty

Question

I'm sure this has been asked somewhere before, but at current I can't seem to find anything fitting to my situation.

Basically I have a table with a column appropriately titled ShipsWithOrder that may contain a key ID or may be null. I need to find a row in the table based on the key ID AND, if the ShipsWithOrder column IS NOT NULL, then i need to get that row as well.

So something like

SELECT * FROM TABLE WHERE KEY = ID OR SHIPSWITH = ID 
--HERES WHERE IT GETS A LITTLE COMPLICATED--
If it found a row where "KEY = ID"
AND that row's "SHIPSWITH" column IS NOT NULL
THEN I need to ALSO include 
    the row whose KEY ID 
        matches the first one's SHIPSWITH value

Thus if Order 123 shipped with order 456, then when i searched for order 123 OR 456 I would get 2 rows returned and not just one, but i would only know 1 value going in. make sense?

Also, would it be possible to make that redundant? I don't think it happens here, but just in case, would it be possible to loop my if/case in this circumstance?

Maybe I should also mention, that just because a row's ship with column isn't null, does not mean that the row with the matching id will have a matching ship with column. most of the time, the secondary row will have a NULL value for its ship with column

If my table looks like this:

+-------+-----------+
|  Key  | ShipsWith |
+-------+-----------+
|   1   |   NULL    |
|   2   |     5     |
|   3   |     6     |
|   4   |   NULL    |
|   5   |   NULL    |
|   6   |   NULL    |
|   7   |     4     |
|   8   |   NULL    |
|   9   |     8     |
+-------+-----------+

And I look for Key 1 then I should get back only 1 row. The row for 1, because it DOES NOT ship with anything, nor does anything ship with it.

However, if I search for 2, then I should get back 2 results. I should get a Row for Key 2 as well as a Row for Key 5 because 5 ships with 2!

@Strawberry then i should get rows `2` and `5` – SpYk3HH Apr 02 '14 at 17:23 — SpYk3HH, Apr 02 '14 at 17:23

Peter · Accepted Answer · 2014-04-02T17:30:40.920

1

Maybe use a UNION:

SELECT * FROM TABLE WHERE KEY = ID OR SHIPSWITH = ID 
UNION
SELECT * FROM TABLE WHERE KEY = (SELECT SHIPSWITH  FROM TABLE WHERE KEY = ID OR SHIPSWITH = ID)

The first part returns the main item (using your original query). The second part returns the ShipsWith item if it exists. If ShipsWith is NULL, nothing is returned for this part of the query. I should point out that this will only go down one level. If you need return a full hierarchy of ShipsWith relationships, then we'll have to do something fancier.

edited Apr 02 '14 at 17:30

answered Apr 02 '14 at 16:57

Peter

12,541
3
34
39

sounds like an idea, but what if `shipswith` is NULL? eh, I'll try something of it – SpYk3HH Apr 02 '14 at 17:06
ok, i need more explanation, please, 'cause this seems to do exactly what i'm asking, but i'm unsure of the possible repercussions! – SpYk3HH Apr 02 '14 at 17:17
Sure. Updated my answer with some explanation. – Peter Apr 02 '14 at 17:29

vp_arth · Answer 2 · 2014-04-02T17:10:59.597

1

select @_ship:=ship ship, t.* from table t where key=ID 
union all 
select ship, t.* from table t where key=@_ship;

There is a fiddle

edited Apr 02 '14 at 17:10

answered Apr 02 '14 at 16:57

vp_arth

14,461
4
37
66

score 1 · Answer 3 · edited May 23 '17 at 10:26

If this goes only one level deep, then it is enough to join with the same table:

SELECT t1.*
FROM Table t1
LEFT OUTER JOIN Table t2 ON t2.ShipsWith = t1.Key
WHERE t1.Key = 123 OR t1.ShipsWith = 123 OR t2.Key = 123

But if orders form longer 'chains':

+-------+-----------+
|  Key  | ShipsWith |
+-------+-----------+
|   1   |     2     |
|   2   |     3     |
|   3   |   NULL    |
+-------+-----------+

then you will need an increasingly big number of joins, e.g.:

SELECT t1.*
FROM Table t1
LEFT OUTER JOIN Table t2 ON t2.ShipsWith = t1.Key
LEFT OUTER JOIN Table t3 ON t3.ShipsWith = t2.Key
WHERE t1.Key = 123 OR t1.ShipsWith = 123 OR t2.Key = 123 OR t3.Key = 123

Obviously, this does not scale very well. For an unlimited chain length, you need recursion. SQL Server has a nice recursive feature called Common Table Expressions (CTE for short). MySQL does not have that (yet). Look here for alternatives:

Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)

seems good, and i'm playing with it, but i keep getting `ambiguous` errors — SpYk3HH, Apr 02 '14 at 17:09
ouch, finally killed ambigous prob, and then ran into redundancy clause problem. locked up server! — SpYk3HH, Apr 02 '14 at 17:10

select all where ID = value, but also grab where ID = column value if not empty

3 Answers3