Union table with a query (1 Viewer)

[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

A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

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

A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

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,

 

[Pat Hartman]

Let me give you a visual for union vs join. Take a piece of paper with a list on it. Take a second list and put the second paper below the first. If the first list had 20 items and the second had 30, you now have 50 items. For a join, put the two pieces of paper side by side and draw lines from the left page to the right page where the IDs match. That is an inner join. The result is a list that is either equal in length to the left sheet if the right sheet has 100% of the same IDs as in the first list or if fewer match, the result will be the rows in the left list that match any rows in the right list. If the right list has multiple matches for the IDs in the left list, the resulting row count will be determined by the number of matches in the right list.