can I ask a SQL question here?

[dgavilanes]

Please let me know


Dennis

 

[Luke M]

Dennis,


Sure thing! Granted, there may not be as many people here who are familiar with SQL, so it may take a little longer to get an answer, but there's no harm in asking. =)

 

[Trader273]

I'd say go for it. Though SQL is a very broad subject, so who knows if you will get an answer. I myself, have been learning T-SQL. Just seems like a natural progression. Learn XL to manipulate the data, then learn some SQL to efficiently get the data.

 

[dgavilanes]

Here you go


I have two tables that I need to merge, however there is a catch, on table "B" , not all depts have data in grades from 3-8, if data is missing I would like to have an empthy row with the grade and dept. Please see below


Table "A" has a field column named grade:


SELECT A.grade

FROM A

GROUP BY grade

HAVING (((A.grade) In (3,4,5,5,6,7,8)));


Table "B"


SELECT .yr, .grade, .dept

FROM

GROUP BY .yr, .grade, .dept

HAVING (((.yr)=2012))

ORDER BY .Dept;


OUTPUT

yr grade dept

2012 6 2030

2012 7 2030

2012 8 2030


Desired final ouput

yr grade dept

2012 3 2030

2012 4 2030

2012 5 2030

2012 6 2030

2012 7 2030

2012 8 2030


Is this possible?

Please advise


Dennis

 

[Kyle McGhee]

Strictly based on the SQL you posted, a Cartesian Product query would provide what you describe.


SELECT B.yr, B.Dept, A.Grade

FROM B, A

GROUP BY B.yr, B.Dept, A.Grade

HAVING (((A.Grade) In (3,4,5,6,7,8)))

ORDER BY B.Dept;

 

[dgavilanes]

Thanks for your effort,


The only way I could get this done was using a MS Access update query, I updated table A with information from table "B" where applicable.


I'm trying to streamline the process with a single sql statement but sometimes its not possible for many reasons. Thank you "All"


Dennis