58896

SQL Query - Table Joining Problems

I am having some serious problems wrapping my head around how to build a proper query for my situation.. Pretty sure it depends on joining the tables properly but I cant seem to figure it out after a lot of googling...

I have the following tables..

Main: ID, WhenDate, InfoText, StatusID, TypeID

and...

Status: ID, StatusText

and...

Secondary: MainID, WhenDate

Hope Im able to explain this correctly.. I have a bunch of records in Main that I need to get info from.. I need to be able to filter on WhenDate and TypeID. I also need to get StatusText from Status table that is linked to StatusID. The problem:</b> The table Secondary links to records in Main, any record in Secondary should result in a row that is identical to the record in Main with the exception that the WhenDate in Secondary is used instead of the WhenDate in Main.

Any help greatly appreciated, even if its just a hint, such as what types of joins to use or something...

Answer1:

I used INNER JOIN for status, assuming that every main record refers to an existing status record. If that is not the case, you may want to change it to a LEFT JOIN.

For the WhenDate, you can just left join Secondary. If a record is found, you can compare against Secondary.WhenDate, otherwise, check against Main.WhenDate.

SELECT m.ID as MainID, m.WhenDate as MainWhenDate, m.InfoText, m.StatusID, st.StatusText, m.TypeID, s.WhenDate as SecondaryWhenDate, CASE WHEN s.MainID IS NULL THEN m.WhenDate ELSE s.WhenDate END AS ActualWhenDate FROM Main m INNER JOIN Status st ON st.ID = m.StatusID LEFT JOIN Secondary s ON s.MainID = m.ID WHERE ( s.MainID IS NULL AND m.WhenDate = <YourDate> OR s.MainID IS NOT NULL AND s.WhenDate = <YourDate> ) AND TypeId = <TypeFilter> AND ... other filters, if you need any ...

Recommend

  • Tomcat filter generates duplicate localhost.log lines
  • duplicate-able inputs validation not working with non duplicate-able fields
  • MySQL JOIN by SELECT earliest DATETIME
  • How can I find the id of a gang in OpenACC?
  • calling bean method on image onClick event [duplicate]
  • Can to do one query from two queries?
  • Is there any better way to write this query
  • Scraping data from the tag names in python
  • 1:1 Alignment of C# Structs with Streamed Delphi Records Possible?
  • How do I scroll HTML textbox to the end?
  • PLSQL Trigger ORA 01403 no data found
  • Iteration over the dictionary and extracting values
  • sub query in SELECT line
  • DropdownlistFor throws an error
  • how to get href link from onclick function in python
  • Making cut/replace methods with JTextArea
  • Foreign key constraint error when adding through migrating
  • Excluding some combinations from left join in MySQL
  • Space between buttons and window
  • Internet Explorer 7 & 8 issue with Prototype 1.6.1 - Could not complete the operation due to err
  • PHP MYSQL event listener
  • react setState sets to string instead of object
  • Acumatica Dynamic MultiSelect Dropdown
  • Conditionally merge primary and secondary dictionary in Ansible
  • Windows Azure VM availability/Failover steps
  • Stack size of secondary threads, significant differences between DEBUG and RELEASE versions
  • Workflow - Database Design
  • calculating number of bytes of each row in an image
  • @tailrec why does this method not compile with 'contains a recursive call not in tail position&
  • Can't remove headers after they are sent
  • Azure table store snapshot/backup capability
  • Zurb Foundation _global.scss meta styles for js?
  • How to use JavaScript to determine whether a file exists in a directory?
  • How to run “Deployd” on port 80 instead of port 5000 in webserver.
  • How do I pass the string value parameter of the selected list item from an auto-populated dropdown l
  • Android full screen on only one activity?
  • Resize panoramic image to fixed size
  • TFS: Get latest causes slow project reloading
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • Weird JavaScript statement, what does it mean?