mysql exists or not exists select query

Question

Table A
ID | date
 1 | 2014-05-16
 2 | 2014-05-15
 3 | 2014-05-16
 4 | 2014-05-17
 5 | 2014-05-18

 Table B
 ID| A.ID   | B.Key | B.KeyValue
 1 | 1      | date  | 2014-07-23
 2 | 3      | date  | 2014-07-11
 3 | 4      | date  | 2013-10-07

Let's say I have two tables, A & B. How do I come up with all of A, but have linked records in B replace the 'date' in A. eg.:

A.1 links up with B.1, and so the new date in A should be 2014-07-23 and not 2014-05-16.

Also, I'd like to have it sorted by the new date. e.g.,

1 | 2014-07-23
3 | 2014-07-11
5 | 2014-05-18
2 | 2014-05-15
4 | 2013-10-07

So far, I have this:

SELECT 
    DISTINCT A.ID, 
    IF(B.Key = 'date', IF(B.KeyValue <> '', B.KeyValue, A.date), A.date) new_date
FROM TableA A
LEFT JOIN TableB B ON A.ID = B.ID
ORDER BY new_date DESC

But some how I come up with duplicates for the linked rows.

should i be using a different approach? eg, perhaps find all those in A that match in B, then UNION ALL with those in A that don't match? what i've found is as long as B.Key is written explicitly in the WHERE clause, it will only find 3 rows instead of 5 — Nick
– Nick, Commented May 22, 2014 at 17:40
try running my query, i just updated it to coincide with your requirements :) — John Ruddell
– John Ruddell, Commented May 22, 2014 at 17:53

John Ruddell · Accepted Answer · 2014-05-22 17:57:19Z

1

if all you want is to show the data in most recent order after putting in the date from b try this. SQL FIDDLE to play with

SELECT 
    a.ID, 
    COALESCE(b_date, a_date) AS updated_date
FROM a
LEFT JOIN b ON a.id = b.a_id
    AND b.key = 'date'
ORDER BY updated_date DESC

edited May 22, 2014 at 17:57

answered May 22, 2014 at 17:49

John Ruddell

25.9k7 gold badges60 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nick Over a year ago

it works but also returns other rows from B; hence why there's a B.Key and B.KeyValue

John Ruddell Over a year ago

ok well then i need some data that will be relevant to it showing up... from what I have to work with it works perfectly.

Nick Over a year ago

just insert another row into B with a value other than date for B.Key. eg, 'page' and B.KeyValue= 865

John Ruddell Over a year ago

try it again i added another condition to join off of.

John Ruddell Over a year ago

also if you added that to the sql fiddle then i need the new link to the fiddle to see it :)

|

marc_s · Accepted Answer · 2016-01-07 19:39:17Z

I am not sure of the rules here in stackoverflow but while trying to answer this question I tried to run these queries in 2 ways which got varying results and was wondering if someone could explain whats going here

I added one record to the b table so my tables look like this

Table A

ID | date
 1 | 2014-05-16
 2 | 2014-05-15
 3 | 2014-05-16
 4 | 2014-05-17
 5 | 2014-05-18

Table B

ID| A.ID   | B.Key | B.KeyValue
1 | 1      | date  | 2014-07-23
2 | 3      | date  | 2014-07-11
3 | 4      | date  | 2013-10-07
4 | 2      | rnd  | 1999-12-31

I ran this query

SELECT 
    a.ID, 
    COALESCE(b_date, a_date) AS updated_date
FROM a
LEFT JOIN b ON a.id = b.a_id and b.key = 'date'
ORDER BY updated_date DESC

and this one

SELECT 
    a.ID, 
    COALESCE(b_date, a_date) AS updated_date
FROM a
LEFT JOIN b ON a.id = b.a_id where b.key = 'date'
ORDER BY updated_date DESC

The difference is one has

and b.key = 'date'

the other has

where b.key = 'date'

Would someone be so kind as to explain what is going on here and why these 2 quires return different results?

Collectives™ on Stack Overflow

mysql exists or not exists select query

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related