CODEWITHSUNDEEP
sql
user3632719
user3632719

Reputation: 47

Complicated SQL query need query structure

I have a SQL query that pulls a result set in the following format

    .       Header1 Header2 Header3 Header4
    Row1
    Row2
    Row3
    Row4

There will always be 4 rows returned from this query.

What I ultimately want to do is make another result set that looks like this:

    .       Header1 Header2 Header3 Header4 Header5 Header6
    Row1
    Row2
    Row3
    Row4

Where [Header5,Row1] is the return value of another query that takes [Header1,Row1] and [Header2,Row1] as parameters

[Header6,Row1] is the return value of a DIFFERENT query that takes [Header1,Row1] and [Header2,Row1] as parameters

I have the first table done fine, and I actually have the entire thing working using for loops in my application code. However, I am migrating the application to a different framework where it would really help (and I believe be much more efficient) if I could do this in one query and I don't know where to start. I am not looking for anyone to write a query, I just need to know the structure of the query that I should use here.

Thanks

Upvotes: 0

Views: 42

Answers (1)

Benjamin M
Benjamin M

Reputation: 24547

How about something like this?

SELECT
  x.h1 AS Header1,
  x.h2 AS Header2,
  x.h3 AS Header3,
  x.h4 AS Header4,
  (SELECT y.h5 FROM y WHERE something with x.h1) AS Header5,
  (SELECT z.h6 FROM z WHERE something with x.h1 and x.h2) AS Header6,
FROM
  x

The subqueries MUST return 0 or 1 result. The query will fail if any subquery returns more than 1 result.

Upvotes: 1

Related Questions