Union table with a query

[Joy83]

Hi
I find this forum very useful specially when I get stack with something that takes me long time to figure it out

I have a query that gets me certain records.
I want to merge the results of this query with another table
Example: the query gives me a lis of department name from a table
Now I want the department details from another table
how to do this?

 

[GPGeorge]

Include the primary and foreign keys for the related tables, as required. Then join the two on those keys as appropriate (primary to foreign keys).

 

[Joy83]

How to refer back to the query?
deal with it as a table
Can you explain more

 

[mike60smart]

Can you upload your database so we can see the structure?

 

[GPGeorge]

Spoiler: etc

How to refer back to the query?
deal with it as a table

Can you explain more

Maybe we both should explain more....

I'll take a guess as to the problem and offer a screenshot which I hope addresses the question. When you want to create a query, you have three options from which to select tables and queries to include.

 

[Joy83]

Thanks a lot for your reply
Unfortunately I can’t share access I can’t do attachments from my laptop

I tried to create it this way but I didn’t get what I want
I want to put the table data beside the IDs that I got from the query
But I am now getting it the other way around

if I have three IDs
I am expecting to see the data beside it and not more than three records

 

[bastanu]

Click the Create\Query Design and add the table and the query. Click on the related field in the table (like DepartmentID) and drag it to the corresponding one in the query. Now select the fields you want to see from each. What you have now is called an equi-join (showing you the results where the fields match in both). You can double-click that line (=join) and select one of the other options to create a left join (so in your example seeing the entire table even if the query does not include certain departments).

SQL | EQUI Join and NON EQUI JOIN - GeeksforGeeks

Your All-in-One Learning Portal: GeeksforGeeks is a comprehensive educational platform that empowers learners across domains-spanning computer science and programming, school education, upskilling, commerce, software tools, competitive exams, and more.

www.geeksforgeeks.org

Cheers,

 

[jdraw]

Joy83,
How about showing readers the SQL of your query?

 

[June7]

Why don't you include the 'other' table in the original query? If you can't provide db, build tables in post to show sample data and structure. Also provide the SQL statement.

 

[GPGeorge]

The more context you provide (I.e. the actual SQL of your query and the tables involved) the better the chance someone can interpret what that properly and give more focused guidance.

For example, "....I tried to create it this way but I didn’t get what I want"

Tell us what you DID get instead. Tell us what you want, specifically. This statement, while relevant, lacks the needed degree of specificity.
"I am expecting to see the data beside it and not more than three records"

 

[Joy83]

Click the Create\Query Design and add the table and the query. Click on the related field in the table (like DepartmentID) and drag it to the corresponding one in the query. Now select the fields you want to see from each. What you have now is called an equi-join (showing you the results where the fields match in both). You can double-click that line (=join) and select one of the other options to create a left join (so in your example seeing the entire table even if the query does not include certain departments).

SQL | EQUI Join and NON EQUI JOIN - GeeksforGeeks

Your All-in-One Learning Portal: GeeksforGeeks is a comprehensive educational platform that empowers learners across domains-spanning computer science and programming, school education, upskilling, commerce, software tools, competitive exams, and more.

www.geeksforgeeks.org

Cheers,

Thanks a lot
It worked

 

[Joy83]

The more context you provide (I.e. the actual SQL of your query and the tables involved) the better the chance someone can interpret what that properly and give more focused guidance.

For example, "....I tried to create it this way but I didn’t get what I want"

Tell us what you DID get instead. Tell us what you want, specifically. This statement, while relevant, lacks the needed degree of specificity.
"I am expecting to see the data beside it and not more than three records"

Thank you for your time and help

 

[bastanu]

Cheers,