Solved

sql select query joining multiple tables with foreign keys

Posted on 2011-09-25
5
457 Views
Last Modified: 2012-05-12
Hi There!

Hoping if someone would be able to help me out?

I am trying to write a select query that joins all tables I have got the following code:
SELECT 
			DATE_FORMAT(OrdersDate, '%M %d, %Y'), 
			LocalOrderNumberValue, 
			OrdersName, 
			OrdersQuantity, 
			InsertSizeValue, 
			InsertStockValue, 
			CoverStockFinishValue, 
			CoverFormatSizeValue, 
			Content, 
			StrategicApplicationValue, 
			DistributionChannelValue, 
			DistributionChannelDetailsValue, 
			AccountValue, 
			Country, 
			IndustrySectorValue, 
			IndustrySubSectorValue
		FROM 
			account,
			content
			country,
			cover_format_size,
			cover_stock_finish,
			distribution_channel,
			distribution_channel_details,
			industry_sector,
			industry_sub_sector,
			insert_size,
			insert_stock,
			local_order_number,
			orders,
			orders_bridge_account,
			strategic_application
		WHERE 
			orders.AccountID = orders_bridge_account.AccountID
			AND orders_bridge_account.OrdersID = orders.OrdersID
			AND orders_bridge_account.AccountID = account.AccountID
			AND orders.ContentID = content.ContentID
			AND orders.CountryID = country.CountryID
			AND orders.CoverFormatSizeID = cover_format_size.CoverFormatSizeID
			AND orders.CoverStockFinishID = cover_stock_finish.CoverStockFinishID
			AND orders.DistributionChannelID = distribution_channel.DistributionChannelID
			AND orders.DistributionChannelDetailsID = distribution_channel_details.DistributionChannelDetailsID
			AND orders.IndustrySectorID = industry_sector.IndustrySectorID
			AND orders.IndustrySubSectorID = industry_sub_sector.IndustrySubSectorID
			AND orders.InsertSizeID = insert_size.InsertSizeID
			AND orders.InsertStockID = insert_stock.InsertStockID
			AND orders.LocalOrderNumberID = local_order_number.LocalOrderNumberID
			AND orders.StrategicApplicationID = strategic_application.StrategicApplicationID
		ORDER BY 
			$order_by 
		LIMIT 
			$start, $display";

Open in new window


I would however like to add to that code a variable that would allow for the results to be refinded. eg. If the user selects country to be equal to South Africa...

Also another example ... if the user would like the date to be between $date1 and $date2 and for the country to be equal to South Africa...

What should I add to my sql query? Would I be able to just add lines of code to the WHERE part... for example:
SELECT
...
FROM
...
WHERE 
			orders.AccountID = orders_bridge_account.AccountID
			AND orders_bridge_account.OrdersID = orders.OrdersID
			AND orders_bridge_account.AccountID = account.AccountID
AND country = $country
AND date = $date
...

Open in new window


What way would be best??

Also just hoping for a little insight into indexes - what index should I make for a statement like this? I'm new to SQL and databases and would be great if I could get a little help !

Thanks so much in advance!!
Chelsesa
0
Comment
Question by:h3rm1t9536
  • 3
5 Comments
 
LVL 15

Expert Comment

by:Eyal
Comment Utility
SELECT
...
FROM
...
WHERE 
			orders.AccountID = orders_bridge_account.AccountID
			AND orders_bridge_account.OrdersID = orders.OrdersID
			AND orders_bridge_account.AccountID = account.AccountID
AND (country = $country OR $country is null)
AND (date = $date or $date is null)
...

Open in new window

0
 
LVL 2

Expert Comment

by:Lord_Death
Comment Utility
declare @WorkGroupID decimal = 0;
SELECT   *
From  ....
WHERE   (@WorkGroupID = 0 OR OwnerWorkGroupID = @WorkGroupID) AND ...
0
 

Author Comment

by:h3rm1t9536
Comment Utility
Hi Eval

Sorry what does it mean when you add '...OR $country is null' to the query? Does it mean select all rows where country is equal to South Africa and where country is equal to NULL?

Sorry and a quick question when I fetch the records and try print them would the code be something like this....
$row['insert_stock.InsertStockValue']

or would it be like this
$row['InsertStockValue']?

Thanks for your help!
0
 
LVL 15

Accepted Solution

by:
Eyal earned 250 total points
Comment Utility
if you pass the parameter $country as NULL so the sql will see it like so... NULL is NULL
that means true and all rows will be returned
if the $country will not be null then this condision will be ignored and country = $country will be applied
0
 
LVL 15

Expert Comment

by:Eyal
Comment Utility
and regarding the print $row['InsertStockValue'] will do
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now