CODEWITHSUNDEEP
mysqlsqlsql-serverstored-proceduresfiltering
jeffry
jeffry

Reputation: 327

Selecting rows based on duplicate values

I basically have results grid and i have a drop down menu on my application which filters the 'Carrier' column. But when selecting a certain carrier I want all the rows returned that have the same dr_id as the Carrier which has been selected.

For example if you look at the picture attached it shows my results grid. If I filter by carrier 'ACE CALL LTD_UK' then I want rows 27, 28, 29 and 30 returned because the dr_id is the same.

enter image description here

Thanks

Upvotes: 0

Views: 122

Answers (1)

XN16
XN16

Reputation: 5879

I don't have a complete solution for you as I don't know exactly what you database schema is (and it is a large stored procedure!). However I do have some suggestions/comments which you might find helpful:

I assume that the stored procedure will currently be returning a single row when they filter is set to 'ACE CALL LTD_UK', if not then this might not be relevant!

What I would do in this case would be to take you SELECT statement and put the results into a CTE, temporary table or nested query. (I'm not sure what SQL DBMS your using, looks like MSSQL, but you also have a MySQL tag for you post).

Once I have those results I would then use a LEFT JOIN from the dr_id in the temp table back to the drm table on the same column. From here you will again need to join to other tables where the data is not distinct, for example the Carrier table, then select the columns that you require.

You could do all this in the existing SELECT statement, however you will have to start joining on tables multiple times or use nested queries, which would get very messy. However the main reason why I chose the solution I have posted is because I don't know the stored procedure well enough and therefore I chose the safest solution.

If you want an example of what I mean, I will try and provide one.

Upvotes: 1

Related Questions