Select statement, Joins? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Select statement, Joins?

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-07-03 : 11:56:51

I have 2 (+) select statements.

(SELECT1)
SELECT * FROM ShippingLoadTable
WHERE ShippingDate = #%s#
AND ProjectNumber = '%s'
ORDER BY Stack ASC

and
(SELECT2)

[SELECT Length, Width, Weight
FROM HollowcorePropertiesTable
WHERE ProjectNumber = '%s'
AND MarkNumber = '%s

now one of the fields in ShippingLoadTable (SELECT1) is the MarkNumber that is the same MarkNumber compared in SELECT2.

How can I Select from the shipping table and while doing that, create a join or something that will automatically give me Length, Width, Weight, that I want from the second select statement?

Any help with this?

Mike B

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-07-03 : 13:10:25

You can try this:
SELECT ShippingLoadTable .* ,
HPT.Length,
HPT.Width, HPT.Weight
FROM ShippingLoadTable LEFT JOIN HollowcorePropertiesTable HPT
ON ShippingLoadTable.ProjectNumber = HPT.ProjectNumber
AND ShippingLoadTable.MarkNumber = HPT.MarkNumber
WHERE ShippingLoadTable.ShippingDate = #%s#
AND ShippingLoadTable.ProjectNumber = '%s'
ORDER BY ShippingLoadTable.Stack ASC

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-07-03 : 13:54:19

I have to remember about this forum. You, YellowBug, are a genius ;). Thank you, that worked exactly how I wanted!

I forgot to ask. There are some fields that I would like to group by, would you know how to add this? I would like to group by, ShippingLoadTable.Stack, and then, ShippingLoadTable.Level. Any ideas?

Also, one more question:
Can the previous Select statement be accomplished with a third table?

I am just learning this SQL language, so, please be a little tolerant! lol :)

Mike B

Edited by - MikeB on 07/03/2002 14:46:17

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-07-04 : 03:40:36

if you specify GROUP BY, you need to add in all the statements in your select list....

To add another table look up JOINS in BOL or on this site. The basic syntax is....


FROM <table>
[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
	[ <join_hint> ]
	JOIN <table>
ON <join expression>

Peace

Rick

Edited by - rickd on 07/04/2002 03:41:07

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-07-04 : 08:04:43

Sorry, what is BOL?

Mike B

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-07-04 : 08:09:33

Books On Line....

Microsofts help books.....

Peace

Rick

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-07-04 : 08:44:53

Thanks for your help. I looked up JOINS in the search on this site and got some help but it doesn't seem to work. I am getting a "syntax error : missing operator in query" message during debug. I use c++ and ADO to manipulate data in an Access database .mdb file. Anyway, the following statement is what I tried. Can you see what is wrong?

 
"SELECT SLT.*, HPT.Length, HPT.Width, HPT.Weight, PIT.ProjectName, PIT.City, PIT.ProvinceOrState 
FROM SLT LEFT JOIN HPT ON SLT.ProjectNumber = HPT.ProjectNumber
		       AND SLT.MarkNumber = HPT.MarkNumber
LEFT JOIN PIT ON SLT.ProjectNumber = PIT.ProjectNumber 
WHERE SLT.ShippingDate = #%s# AND SLT.ProjectNumber = '%s'
ORDER BY SLT.Stack ASC"

Also, why use JOINS, I just tried the following and it seemed to work:
[code]
"SELECT SLT.*, HPT.Length, HPT.Width, HPT.Weight, PIT.ProjectName, PIT.City, PIT.ProvinceOrState
FROM SLT, HPT, PIT
WHERE SLT.ProjectNumber = HPT.ProjectNumber
AND SLT.MarkNumber = HPT.MarkNumber
AND SLT.ProjectNumber = PIT.ProjectNumber
AND SLT.ShippingDate = #%s#
AND SLT.ProjectNumber = '%s'
ORDER BY SLT.Stack ASC"

Any help with this?
Mike B

Edited by - MikeB on 07/04/2002 09:05:17

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-07-04 : 09:10:47

You are missing the OUTER part of the join....

also, check the value of your variables. put the whole statement into a variable and post the result...

Peace

Rick

Edited by - rickd on 07/04/2002 09:14:57

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-07-04 : 09:33:35

I am just learning, SQL, could you help me out with this?

quote:
You are missing the OUTER part of the join....

Mike B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources