0

Am a front-end dev. Let's get that out of the way... My issue is I need to get some data out of a few tables within the DB. The backend is written in C#. (I will try to answer questions on it if need be) which is not my area.

My SQL is just about average, I can understand enough to write the query that I need see below: (I've sanitized the table names)

SELECT *
FROM (SELECT * FROM table_1 UNION SELECT * FROM table_2) name
JOIN (SELECT OrderNo, ID FROM table_3 UNION SELECT OrderNo, ID from table_4) name_2
ON name.OrderNo = name_2.OrderNo
WHERE name_2 = <queryParam>

The difficult part is that I need to be able to run this query by varying search terms, by order number, by telephone number and by branch ID using linq.

Can anyone help and also point me in the direction of (preferably easy) resources to learn?

Rue
  • 49
  • 1
  • 10
  • So you are trying to search words from multiple fields? I.e. if "123" matched both order number "12345" and phone number "525-123-5555" you would want both? – thalacker Nov 17 '20 at 16:16
  • Also, why would you union `table_1` and `table_2`? Perhaps a basic structure of your tables would be a better way to represent your thoughts for this question. – thalacker Nov 17 '20 at 16:19
  • Searching for all records that match either a phone number (type long) or an order number (type string). `table_2` and `table_4` are the archived versions of 1 and 3. I don't know why. But basically if the information isn't in tables 1 and 3 then they have been moved to tables 2 and 4. Why? I can't say. It's what I've inherited from the Backend devs. @thalacker – Rue Nov 17 '20 at 16:26
  • 1
    A resource for you might be this Microsoft documentation (https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/basic-linq-query-operations#filtering). This should be pretty straitforward. Just do the "from" for the two tables and then compare your search term with an `||` operator. – thalacker Nov 17 '20 at 16:45
  • @Rue Just out of curiosity. If `table2` and `table4` are archive tables (potentially have massive amount of data) then why do you include them in your query? Shouldn't they be treated as fallback? If the query haven't found anything in `table1` and `table3` then issue an query against the archive ones. – Peter Csala Nov 18 '20 at 08:11
  • So the way it's been explained to me is generated data for the tables is stored in `table_1` for an indeterminate amount of time, when the be team feel there is too much they move it into `table_2` they do the same with 3 and 4. So on the front-end when the user performs a search it often comes back with no results, this is because the data has been moved out of one table into another, I only found what I needed using that SQL query and none of the back-end devs can convert it to Linq C# for me – Rue Nov 18 '20 at 09:15
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. Can you provide more details about what you mean by "varying search terms"? – NetMage Nov 19 '20 at 22:49

0 Answers0