CODEWITHSUNDEEP
mysqlcoldfusion
Ocssor
Ocssor

Reputation: 64

Bring back a certain record from a table, unless a similar record exists, in which case bring back that one

Assume that I have a single table, with 3 records, [a], [b], [c].

I want to get back a, b, c, until d is created. D can be a duplication of either a, b or c, except the value in one column: x will be different.

N.B: My DB is MySQL, my back-end in ColdFusion and when I say 'user edits' or 'user sees a list', I mean that the user sees a web page list of items to edit. If you think it would be better done programmatically as opposed to the database doing the work, I would appreciate the input.

So what I'd like is that:

etc...

It seems like it should be a case of SELECT [fooRecord] UNLESS [barRecord exists] in which case bring back [barRecord]. I've looked around for similar answers, and some do use EXISTS, but that only seems to return a true or false value, not the record I'm looking for. I believe it's a fancy sub query or INNER JOIN that I need, but I'm at a loss to think up the exact syntax to do it.

This is my first time posting on SO so I apologise if I've done any part of it incorrectly or my question isn't clear, I will provide clarity or further info if asked for. Any help much appreciated.

Upvotes: 1

Views: 81

Answers (3)

Ocssor
Ocssor

Reputation: 64

Arrived on the answer after some discussion with the team:

SELECT * FROM table1 t1 WHERE 1=1 AND ( x = <cfqueryparam value="#arguments.x#"> OR ( x is null and y not in ( SELECT t2.et_code FROM table2 t2 WHERE t2.x = <cfqueryparam value="#arguments.x#"> ) ) )

I tried to format the answer so that it's easily readable. Thank you for the other answers.

Upvotes: 1

Jarede
Jarede

Reputation: 3488

I think, and I might be wrong so please correct me, that this is a NoSQL pattern. In that I, JaredE exist as a user in the users table, but my userid isn't the primary key of that table and thus anytime I or someone else edits my record it just inserts a new record rather than updates my existing record.

If you were to use this pattern, then you'd be doing something like

select distinct userid, a,b,c, lastmodified
  from tbl
 ORDER BY lastmodified desc

Upvotes: 1

Mark A Kruger
Mark A Kruger

Reputation: 7193

Your schema seems a bit convoluted - might be time to rethink that. Often the issue is an overwrought attempt to avoid additional tables or columns - but this might a case for additional meta data added to the schema to help pull this data. You might also consider simply selecting ALL the data (a,b,c,d,e) and simply using Q of a Q to tease out the row you want based on X.

Meanwhile, a CASE statement might get you there. This is total untested psuedo-code but it might give you an idea.

<Cfquery>
    SELECT  
        CASE
            WHEN x = *condition* THEN orig.col1 
            ELSE    mod.col1 
        END CASE
        AS col1,
        CASE
            WHEN x = *condition* THEN orig.col2
            ELSE    mod.col2
        END CASE
        AS col2
    FROM  tablename orig OUTER JOIN tablename mod
            ON (orig.col1 = mod.col1 
                AND orig.col2 = mod.col2
                AND orig.x <> orig.x)       
</cfquery>

What I'm trying to do is join the 2 tables together and tease out one value vs the other based on whether or not it exists. I'm not sure I'm quite there with this query but you get the idea.

Another approach would be a UNION - something like this:

<Cfquery>
        SELECT  
        col1, col2, col3
        FROM tablename 
        WHERE *primarykey* NOT IN
            (*select rows that DO have duplicates with modified x*)
    UNION ALL
        SELECT  
        col1, col2, col3
        FROM tablename 
        WHERE *primarykey* IN
            (*select rows that WITH the modified x*)        
</cfquery>

This has the advantage of being a bit easier to understand. You can work with one query, then the other, then join them together to get what you want.

Good luck - seems daunting :)

Upvotes: 1

Related Questions